locked
Running totals parallel periods (years) RRS feed

  • Question

  • Hi Guys,

    I'm new to DAX and I'm trying to track sales evolution by Month for parallel periods (last 3 years). 

    The results should be displayed in a matrix in Power BI and consequently in a line chart.

    In very short, the data model consists in a star schema composed of a dimCalendar( DateID, FullDate, Month, MonthOfYear, Year) dimension table and a factSales(DateID, (multiple other FK fields), SalesAmt) fact table. 

    In Excel the source table would look like:

    Sales Amount Year
    Moth 2017 2018 2019
    Jan $37,500 $35,300 $32,300
    Feb $22,900 $21,600 $19,200
    Mar $24,900 $21,900
    Apr $26,600 $26,300
    May $24,600 $22,400
    Jun $18,300 $17,800
    Jul $20,900 $20,200
    Aug $16,000 $16,200
    Sep $22,600 $20,300
    Oct $43,800 $43,100
    Nov $22,800 $23,200
    Dec $17,800 $19,600
    Grand Total $298,700 $287,900 $51,500

    While the destination table would look like:

    Running Sales Amount Year
    Moth 2017 2018 2019
    Jan $37,500 $35,300 $32,300
    Feb $60,400 $56,900 $51,500
    Mar $85,300 $78,800 $51,500
    Apr $111,900 $105,100 $51,500
    May $136,500 $127,500 $51,500
    Jun $154,800 $145,300 $51,500
    Jul $175,700 $165,500 $51,500
    Aug $191,700 $181,700 $51,500
    Sep $214,300 $202,000 $51,500
    Oct $258,100 $245,100 $51,500
    Nov $280,900 $268,300 $51,500
    Dec $298,700 $287,900 $51,500

    How could the above outcome be achieved in DAX? The new metrics should also be responsive to the filtering context applied against factSales (there are many other dimension tables not described in the model in order to keep things conceptually simple).

    Thanks in advance

    Tuesday, March 5, 2019 9:50 PM

Answers

  • Problem solved courtesy of:

     https://community.powerbi.com/t5/Desktop/Running-total-over-years/m-p/585482#M277410 

    Running Sales Amount = 
    VAR CurrDate =
        MAX ( dimCalendar[FullDate] )
    RETURN
        IF (
            COUNTROWS ( 'factSales' ) > 0,
            CALCULATE (
                SUM('factSales'[SalesAmt]),
                FILTER (
                    ALLSELECTED ( 'dimCalendar' ),
                    'dimCalendar'[FullDate] <= CurrDate
                        && 'dimCalendar'[Year] = YEAR ( CurrDate )
                )
            ),
            BLANK ()
        )

    • Marked as answer by MarioBI Tuesday, March 5, 2019 10:56 PM
    Tuesday, March 5, 2019 10:55 PM