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]<=...
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!

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.

       

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

        Hi newbie9292

         

        You just need to select 2020 in the slicer.

         

        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!