DATEADD not calculating correct previous month totals

Power BI
1

I’ve seen this work in your 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:

 Sales LM = calculate ( [Total Sales] , dateadd( PBI_FSCAPF[PADDATE], -1, MONTH ))

       and

 Sales LQ = calculate ( [Total Sales] , dateadd( PBI_FSCAPF[PADDATE], -1, QUARTER ))

Capture17
Capture17.JPG582×634 65.1 KB

PBI_FSCAPF is my dedicated date table and PADDATE is my date field formatted as mm/dd/yyyy. YEAR-MO and YEAR-QTR are also in the date table.

My date table 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).

Capture18.JPG

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…)!

2

This has to be to do with something in your data or model that isn’t lining up. These formulas are never wrong if the setup is correct.

Are you sure that the Month-Year column perfecting aligns with the true month?

I would also change the date format to be for where you are located as it may be getting confused around the actual date logic.

I would honestly also use the date table that I recommend and use in all my own models. It’s easy to setup and you can also add to it depending on the information and filters that you need.

http://portal.enterprisedna.co/courses/powerbisuperuserworkshop/lectures/1772347

3

I should have mentioned that my custom calendar is a 4-4-5 calendar. You actually solved the problem for me via this You-tube video " Calculating Month On Month Change - Custom (445) Calendars, Power BI and DAX."

I followed your measure to calculate the previous month’s Sales and it worked perfectly. One thing you mentioned in the video is that normal time intelligence functions don’t normally work with custom financial calendars.

Thank you for your help!

Rose