Help with DAX query

[newbie9292]

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. 

[newbie9292]

@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 · Accepted solution]

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]

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]

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!

[v-xulin-mstf]

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]

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]

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!

[v-xulin-mstf]

Hi @newbie9292,

In the measure I mentioned above, when 2021 is selected, Running Total returns data from 2021 and Ly Running Total returns data from 2020.

Please check.

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!

[FrankAT]

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]

@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?