Year to Date Calculations with Slicer Filtering

Power BI
1

Hi everyone!

I have completed the financial reporting with Power BI course and am making financial statements based on my learnings in this course.

I am currently building an income statement and would like to add a “Last 12 Months” and “Last 24 Months” DAX calculation based on a month and year slicer on the report page.

I have created a DAX measure to represent the selected date:

Selected Date = Max(‘Date’[Date])

I have then added the following expression to calculate the Financial Value measure for the last 12 months based on the selected date (-24 for last 24 months):

Last 12 Months =
CALCULATE (
[Financial Value],
DATESINPERIOD ( ‘Date’[Date],
[Selected Date],
-12,
MONTH))

The issue I am running in to, is that if the year slicer is set to a specific year- it will only do the calculation based on that year, and not based on the underlying data of the past 12/24 months. Is there any way ensure that data from the last 12/24 months is being considered, regardless of the page’s slicer filters?

2

Hi @DajanaErak
The issue you are facing is the context created by the slicer.
So you need to use the ALL syntax to overcome this.

I tend to use variables to make the end calculate easier to read. And if you’re using @Melissa’s date table then use period offsets to set the date filter.

Assuming you are working with calendar year accounting this might look like this:

Last 12 Months =
Var vSelectedOffset = CALCULATE(Max(MonthOffset),Dates[Date]=[Selected Date])

Var vDateRange = FILTER(All(Dates),[MonthOffset] > vSelectedOffset-12 && [MonthOffset]<=vSelectedOffset)

Var vResult = CALCULATE([Financial Value],vDateRange)

Return
vResult

Without a PBIX it’s a bit tricky to give you the correct DAX but the principles here are:

  • Identify the MonthOffset for the selected date

  • Filter the dates for that offset minus 12

  • Apply that filter to the Calculate function of your measure

I hope this helps. If you need more help here and you can share a PBIX then we can work through this some more

Thanks
Pete

1 Like
3

Hello @DajanaErak

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

4

Hi @DajanaErak

We’ve noticed that no response was received from you on the post above.

Just following up if you still require assistance to solve your inquiry.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Hi @DajanaErak

Due to inactivity, a response on this post has been tagged as “Solution”.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.