Forum Discussion

newbie9292's avatar
newbie9292
Helper II
5 years ago
Solved

Help with DAX query

Hi,

I am new to power bi and DAX world. I am trying to get data using a DAX query : 

Last_year_Kum_AE_Combined = CALCULATE(SUM(AE_Combined[AuftrEing]),FILTER(ALLSELECTED(Date_dim),Date_dim[Date]<=MAX(Date_dim[Date])))
 
Which gets the cummulative data. I want to filter this query to get only the cummulative data for the year 2020. 
 
Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi newbie9292

     

    You can create two measures as:

    Running Total = 
CALCULATE (
    SUM('Table'[Sales]),
    FILTER (
        ALLSELECTED('Table'[Date]),
        'Table'[Date] <= MAX ('Table'[Date] ) && YEAR('Table'[Date])=YEAR(MAX('Table'[Date])) 
    )
)
    Ly Running Total = 
CALCULATE (
    SUM('Table'[Sales]),
    FILTER (
        ALLSELECTED('Table'[Date]),
        'Table'[Date] <= MAX ('Table'[Date] ) && YEAR('Table'[Date])=YEAR(MAX('Table'[Date])) && DATEADD('Table'[Date],-1,YEAR)
    )
)

     

    If you still have some question, please don't hesitate to let me known.‌‌

     

    Best Regards,

    Link

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

10 Replies

  • FrankAT's avatar
    FrankAT
    Community Champion
    5 years ago

    Hi newbie9292 ,

    the following example use the year 2017 in the measure. Adjust it to year 2020:

     

     

     

    Running Total of 2017 = 
CALCULATE (
    [Total Sales],
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[CalendarYear] = MAX ( 'Calendar'[CalendarYear] )
            && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
            && 'Calendar'[CalendarYear] = 2017
    )
)

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

     

     

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago

    newbie9292 

     

    what is the end goal? You can filter in Power Query, report filters, slicer and DAX, but I'm not convinced that DAX is the best place for your filter without understanding more about your requirements?

  • newbie9292's avatar
    newbie9292
    Helper II
    5 years ago

     

    FrankAT Thank you for your solution. Although I was able to get the cummulative data for the year 2021 with your DAX query, I am struggling to get the data for the year 2020 which is in the same table. 

    I am trying to get a visual like the above. The yellow line represents 2020(last year) data.

     

    But when I select both year 2020 and 2021 in the slicer, this is what I get : 

     

     

    • v-xulin-mstf's avatar
      v-xulin-mstf
      Community Support
      5 years ago

      Hi newbie9292

       

      You can create two measures as:

      Running Total = 
CALCULATE (
    SUM('Table'[Sales]),
    FILTER (
        ALLSELECTED('Table'[Date]),
        'Table'[Date] <= MAX ('Table'[Date] ) && YEAR('Table'[Date])=YEAR(MAX('Table'[Date])) 
    )
)
      Ly Running Total = 
CALCULATE (
    SUM('Table'[Sales]),
    FILTER (
        ALLSELECTED('Table'[Date]),
        'Table'[Date] <= MAX ('Table'[Date] ) && YEAR('Table'[Date])=YEAR(MAX('Table'[Date])) && DATEADD('Table'[Date],-1,YEAR)
    )
)

       

      If you still have some question, please don't hesitate to let me known.‌‌

       

      Best Regards,

      Link

       

      Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

      • newbie9292's avatar
        newbie9292
        Helper II
        5 years ago

        Hello Link,

        Thanks for your reply. I tried your suggested solution but for some reason I am getting the same values for 2020 and 2021.