Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Roseventura
Responsive Resident
Responsive Resident

DATEADD not calculating correct totals

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:

 

     Sales LM = calculate ( [Total Sales] , dateadd( PBI_FSCAPF[PADDATE], -1, MONTH ))
 
          and
 
     Sales LQ = calculate ( [Total Sales] , dateadd( PBI_FSCAPF[PADDATE], -1, QUARTER ))
 
PBI_FSCAPF is my date table and PADDATE is my date field formatted as mm/dd/yyyy.  YEAR-MO and YEAR-QTR are also in the date table.
 
 
Capture17.JPG
 
Has anyone ever seen this before?
 
Thanks,
Rose
 

 

1 ACCEPTED SOLUTION

Ashish,

 

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:

 

Prev Month Sales =
     var CurrentMonth = SELECTEDVALUE( PBI_FSCAPF[CAFMON] )
     var CurrentYear = SELECTEDVALUE( PBI_FSCAPF[PADFYR] )
     var MaxMonthNum = CALCULATE( MAX( PBI_FSCAPF[CAFMON] ) , ALL(PBI_FSCAPF) )
RETURN
IF( HASONEVALUE( PBI_FSCAPF[CAFMON] ) ,
     SUMX(
          FILTER ( ALL (PBI_FSCAPF),
               IF ( CurrentMonth = 1,
                    PBI_FSCAPF[CAFMON] = MaxMonthNum && PBI_FSCAPF[PADFYR] = CurrentYear - 1,
                    PBI_FSCAPF[CAFMON] = CurrentMonth -1 && PBI_FSCAPF[PADFYR] = CurrentYear ) ),
              [Total Sales] ),
     BLANK() )
 
Credit goes to Sam McKay of Enterprise DNA.

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

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. 

Nick,

 

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).

 

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...)

 

Any other suggestions?

Anonymous
Not applicable

Ah sorry, I see that you put that in your original post now Smiley Happy.

 

Any chance you post some sample data?  There's always a reason for things, just gotta find it

How do I post sample data?  Do you need the PBIX file?  I don't see an icon where I can upload a file (only jpgs).

 

Please advise.

 

Rose

 

Hi @Roseventura ,

 

Please upload your files to One Drive and share the link here.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

It's asking for a password. Can you add it just using onedrive or drop box?

Hi,

 

In your PBI_FSCAPF Table, 29-01-2012 is falling in 2012-02.  Why is that so?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Our fiscal calendar is 4-4-5, not a conventional monthly calendar so fiscal month January 2012 started on 1/1/2012 and ended on 1/28/2012.  The fiscal month February started on 1/29/2012 and ended on 2/26/2012, and so on.

 

The field CAFMON is the column that identifies what the fiscal month number is.

 

The entire file is built on the 4-4-5 fiscal calendar.

 

Rose

 

 

Hi,

 

Try this measure for computing sales of last month

 

Sales LM = CALCULATE([Total Sales],DATESBETWEEN(PBI_FSCAPF[PADDATE],MIN(PBI_FSCAPF[PADDATE])-if(mod(month(MIN(PBI_FSCAPF[PADDATE])),3)=0,35,28),MIN(PBI_FSCAPF[PADDATE])-1))
Hope this helps.
 
Untitled.png

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Ashish,

 

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:

 

Prev Month Sales =
     var CurrentMonth = SELECTEDVALUE( PBI_FSCAPF[CAFMON] )
     var CurrentYear = SELECTEDVALUE( PBI_FSCAPF[PADFYR] )
     var MaxMonthNum = CALCULATE( MAX( PBI_FSCAPF[CAFMON] ) , ALL(PBI_FSCAPF) )
RETURN
IF( HASONEVALUE( PBI_FSCAPF[CAFMON] ) ,
     SUMX(
          FILTER ( ALL (PBI_FSCAPF),
               IF ( CurrentMonth = 1,
                    PBI_FSCAPF[CAFMON] = MaxMonthNum && PBI_FSCAPF[PADFYR] = CurrentYear - 1,
                    PBI_FSCAPF[CAFMON] = CurrentMonth -1 && PBI_FSCAPF[PADFYR] = CurrentYear ) ),
              [Total Sales] ),
     BLANK() )
 
Credit goes to Sam McKay of Enterprise DNA.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.