Forum Discussion

newbie9292's avatar
newbie9292
Icon for Helper II rankHelper 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
Icon for Helper II rankHelper 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
Icon for Community Support rankCommunity 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
    Icon for Helper II rankHelper 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
      Icon for Community Support rankCommunity 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!

      • newbie9292's avatar
        newbie9292
        Icon for Helper II rankHelper II
        5 years ago

        Hello Link,

         

        The problem is I want to visualize both 2020 and 2021 data on the same visual, 

         

        The yellow line should be the Last year 2020 data and the dark bar should represent the 2021 data like the way it is shown.

        I have 2020 and 2021 data in the same data table.

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

      Hi newbie9292

       

      Is your issue solved?

      If the issue has been solved, please adopt the solution to help others.

      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!