cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amorts Regular Visitor
Regular Visitor

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

Accepted Solutions
amorts Regular Visitor
Regular Visitor

Re: DAX to obtain previous day sales

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?

11 REPLIES 11
Super User
Super User

Re: DAX to obtain previous day sales

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))
amorts Regular Visitor
Regular Visitor

Re: DAX to obtain previous day sales

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

 

Super User
Super User

Re: DAX to obtain previous day sales

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

amorts Regular Visitor
Regular Visitor

Re: DAX to obtain previous day sales

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

Super User
Super User

Re: DAX to obtain previous day sales

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)
amorts Regular Visitor
Regular Visitor

Re: DAX to obtain previous day sales

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

 

 

Super User
Super User

Re: DAX to obtain previous day sales

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

Highlighted
amorts Regular Visitor
Regular Visitor

Re: DAX to obtain previous day sales

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

amorts Regular Visitor
Regular Visitor

Re: DAX to obtain previous day sales

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?