I've seen this work in other people's examples, but I can never get it to work in mine.
My DATEADD() measure is not calculating the correct totals for the previous month or previous quarter. Here are my measures:
Solved! Go to Solution.
Your measure came close, but not every previous month was accurate. I found a you-tube video that demonstrated how to calculate the previous month's sales and that worked perfectly.
When you have custom calendars like mine, normal time intelligence functions don't work. Here is my measure:
To use time-intelligence functions correctly you need a dedicate Date table, look at the Calendar function if you dont have one. Then set that table to a Date Table ( PBI Desktop--> Modeling--> Calendars--> Mark as Date Tabel).
Then in the measures where it is looking for a date column, you will use this new Calendar table. GIve it a shot and come back with any questions that arise.
I do have a dedicated date table. That's what PBI_FSCAPF is, and yes, it is flagged as a "date table" with PADDATE as the date field (all dates are unique).
I'm joining my shipments file with my date table using Shipments[Transaction Date] to PBI_FSCAPF[PADDATE] (see below).
I also checked to see if it was a difference between fiscal and calendar (my date table is layed out as fiscal), but that wasn't it either. If you look at Total Sales for FISCAL June 2017, the total is $19,753,438. Total Sales for CALENDAR June 2017 is $17,954,667. On the table under July 2017, it shows previous month's Sales (June 2017) as $16,073,574 which is actually sales for the dates 6/2/17 thru 6/29/17. Even if DATEADD didn't work with a fiscal calendar, why would it only take sales on those dates and not the whole month?
P.S. 6/1/17 and 6/3017 were NOT weekend days (not that that should matter...)
Any other suggestions?
Hi @Roseventura ,
Please upload your files to One Drive and share the link here.