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
Anonymous
Not applicable

DAX to obtain previous day sales

Totally stuck on this one, I've tried PREVIOUSDAY and this formula

 

Total Policy Count Prev Day = CALCULATE(sumx('LGIDW SOP', 'LGIDW SOP'[Policy Volume]),FILTER('LGIDW SOP','LGIDW SOP'[App Submitted Date] = max('System Submitted Date'[Date]) - 1), all('LGIDW SOP')) 

 

without success.

 

All I want to do is calculate the previous day's sales volume so I can create a measure to provide a daily % change. An example of what I want to be able to do is the Total Policy Count Prev Day and % Change Columns below.

 

I am sure it should be simple!

 

DateTotal Policy CountTotal Policy Count Prev Day% Change
01/04/2017 00:006000100%
02/04/2017 00:00567600-6%
03/04/2017 00:00258567-120%
04/04/2017 00:00214925888%
05/04/2017 00:002377214910%
06/04/2017 00:00238523770%
07/04/2017 00:0018392385-30%
08/04/2017 00:002092183912%
09/04/2017 00:004782092-338%
10/04/2017 00:00178478-169%
11/04/2017 00:00217417892%
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hello, I managed to solve this in the end.

 

Total Policy Count Prev Day = calculate(sum('LGIDW SOP'[Policy Volume]),filter(all('System Submitted Date'[Date]), 'System Submitted Date'[Date]=max('System Submitted Date'[Date])))

 

Perhaps the other posted solutions could have worked if my date table did not run through to the end of the year?

View solution in original post

12 REPLIES 12
Stachu
Community Champion
Community Champion

try this, I made the logic a bit more complex to cover for days without sales

Column = 
VAR CurrentDate = 'LGIDW SOP'[Date]
VAR PreviousDate = CALCULATE(LASTDATE('LGIDW SOP'[Date]),FILTER('LGIDW SOP','LGIDW SOP'[Date]<CurrentDate))
RETURN
CALCULATE(SUM('LGIDW SOP'[Policy Volume]),FILTER('LGIDW SOP','LGIDW SOP'[Date]=PreviousDate))


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

Thanks for your help on this. Unfortuntely it does work for me, I tried a couple of variations:

 

Column = 
VAR CurrentDate = 'LGIDW SOP'[App Submitted Date]
VAR PreviousDate = CALCULATE(LASTDATE('LGIDW SOP'[App Submitted Date]),FILTER('LGIDW SOP','LGIDW SOP'[App Submitted Date]<CurrentDate))
RETURN
CALCULATE(SUM('LGIDW SOP'[Policy Volume]),FILTER('LGIDW SOP','LGIDW SOP'[App Submitted Date]=PreviousDate))

It didn't like this as a single value for App Submitted Date cannot be determined.....so I tried this...

 

 

 

Column = 
VAR CurrentDate = max('LGIDW SOP'[App Submitted Date])
VAR PreviousDate = CALCULATE(LASTDATE('LGIDW SOP'[App Submitted Date]),FILTER('LGIDW SOP','LGIDW SOP'[App Submitted Date]<CurrentDate))
RETURN
CALCULATE(SUM('LGIDW SOP'[Policy Volume]),FILTER('LGIDW SOP','LGIDW SOP'[App Submitted Date]=PreviousDate))

which returned no results...

 

 

Column = 
VAR CurrentDate = today()
VAR PreviousDate = CALCULATE(LASTDATE('LGIDW SOP'[App Submitted Date]),FILTER('LGIDW SOP','LGIDW SOP'[App Submitted Date]<CurrentDate))
RETURN
CALCULATE(SUM('LGIDW SOP'[Policy Volume]),FILTER('LGIDW SOP','LGIDW SOP'[App Submitted Date]=PreviousDate))

...as did this I;m afraid

 

Stachu
Community Champion
Community Champion

the syntax I posted is for calculated column, were you looking for a measure syntax?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

I did apply this as a column but I am looking for a measure yes, sorry I should have stated this originally

Stachu
Community Champion
Community Champion

try these

Total Policy Count = SUM('LGIDW SOP'[Policy Volume]) 

Total Policy Count PrevDay = 
VAR CurrentDate = LASTDATE('LGIDW SOP'[App Submitted Date])
VAR PreviousDate = CALCULATE(LASTDATE('LGIDW SOP'[App Submitted Date]), FILTER(ALL('LGIDW SOP'[App Submitted Date]),'LGIDW SOP'[App Submitted Date]<CurrentDate))
RETURN
CALCULATE([Total Policy Count],'LGIDW SOP'[App Submitted Date]=PreviousDate)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

I tried that and it still returns blanks. I cannot work out why it is not working?!

 

 

Stachu
Community Champion
Community Champion

are you using 'LGIDW SOP'[App Submitted Date] in the visual, or is there a calendar table that yo use?
I cannot replicate the issue on my side
Capture.PNG



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

I've tried it out in an empty workbook with the same results....obviously I am doing something slightly different.

 

Is there a way I can upload the workbook here?

Hi @Anonymous,

 

You can upload your file to a cloud drive like OneDrive, GoogleDrive then share the download link here.

 

Best Regards,

Dale

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

Hello, I managed to solve this in the end.

 

Total Policy Count Prev Day = calculate(sum('LGIDW SOP'[Policy Volume]),filter(all('System Submitted Date'[Date]), 'System Submitted Date'[Date]=max('System Submitted Date'[Date])))

 

Perhaps the other posted solutions could have worked if my date table did not run through to the end of the year?

Anonymous
Not applicable

I don't see how your formula can work because there is nothing in it which points to the previous day. Or perhaps you've posted the wrong formula?

Anonymous
Not applicable

I do have a calendar date table, 'System Submitted Date'.

 

I have been using only the LGIDW SOP table for the purposes of this discussion...let me try it in a new workbook

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.