Cumulative totals (12 months), independent of the year slicer

[dimbroane]

Hello

I am amending the post of yesterday evening, to include a data set based on fictitious numbers.

I have a cumulative totals problem that I have been trying to resolve for the entire day, without success.

I want to show in a table (twelve months - months coming from the Date table), the

• actuals for the current year
• actuals for the previous year
• actuals for two years ago
• actuals for three years ago
  And
• cumulative actuals for the current year
• cumulative actuals in the prior year
• cumulative actuals for two years ago
• cumulative actuals for three years ago

The cumulative totals must show the total from the start of the year (July) to the current row context.

I have written the cumulative formulas using ALLSELECTED( Dates[Date] )

The PowerBI model: Example.pbix (86.1 KB)
The PowerBI model is constructed from this Excel file: Example.xlsx (368.7 KB)

You can see that the only formula that works is the cumulative total for the current year.
Any cumulative total for the previous years does not work.

Why do the previous years not work? It does not make any sense.

I marked the Dates table as a “date” table.

I would be very grateful for your help.

PS: @BrianJ
There is no mismatch in granularities; I linked the Fact table to the Date table; I am showing 12 months.
Had there been a mismatch, no cumulative total would have worked. As such, the current year does work; the previous years do not.

[BrianJ]

@dimbroane,

It’s difficult to figure out exactly what’s going on here without the full file to work with. However, I strongly suspect one problem is related to the mismatch in granularities within your cumulative total formulas (actuals operating at the monthly level, while date operating at the daily level). Have you considered allocating your actual data to the daily level using this allocation approach?:

https://forum.enterprisedna.co/t/the-ultimate-budget-allocation-methodology-for-power-bi-analysis/4069

after which you could create a direct relationship from the date table to the result table using Dates[date], which would both simplify your model and DAX, as well as increase the range of time intelligence functions available to you.

• Brian

[Nick_M]

To piggy-back onto Brian’s reply:

1. Do not hard code values into your formulas, liked Dates= 2018
2. You can nest time intelligence functions.

So you can have:
Total Sales= SUM (Sales[Sales])

Then sales of last year:

CALCULATE(

[Total Sales],

SAMEPERIODLASTYEAR('Date'[Date])

)

Then from there you can mix in the other time inteligence functions to get the figure you are looking for.

Also be sure that your Date Table is marked as such

[uriah1977]

Hello Dimbroane,

When I follow the relations in your tables I think the right dates/months/years are not flowing through them. I have added a picture to show you the difference between the EOM and Perpose columns that are part of that flow.

I have attached a PBIX file with your example where I added Date table. In the Power Query Editor you can use the function Add column by example to make a column with the first date of the month based on the Permonth. In that manner you can link the Date table directly to the F

Example.pbix (130.8 KB) act table without the link table.
I hope it works out for you.

[dimbroane]

After I watched a few of Sam’s videos, I have finally realised that the year filter context cannot be ignored (or maybe it could, but I cannot figure how).

That year filter context comes from the year slicer.

The first example uses the End Of Month:

While the second example uses the Name of Month:

It does not matter what dimension I use from the Calendar table, so long it’s linked to the Fact table and, more importantly, I use the year slicer to tell DAX what I want to summarise.

Following @uriah1977 example, I removed the intermediary table between the Calendar table and the Fact table (although I am not sure it did make a difference).

Example.pbix (2.1 MB)

Many thanks all, for your support; I spent practically the entire day of Friday trying to fix it.
The addition of a small year slicer to the canvas won’t trouble anybody.

Regards

Doru