cancel
Showing results for
Search instead for
Did you mean:
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!

 Date Total Policy Count Total Policy Count Prev Day % Change 01/04/2017 00:00 600 0 100% 02/04/2017 00:00 567 600 -6% 03/04/2017 00:00 258 567 -120% 04/04/2017 00:00 2149 258 88% 05/04/2017 00:00 2377 2149 10% 06/04/2017 00:00 2385 2377 0% 07/04/2017 00:00 1839 2385 -30% 08/04/2017 00:00 2092 1839 12% 09/04/2017 00:00 478 2092 -338% 10/04/2017 00:00 178 478 -169% 11/04/2017 00:00 2174 178 92%
1 ACCEPTED SOLUTION

Accepted Solutions
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

## 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))```
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

## Re: DAX to obtain previous day sales

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

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

## 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)```
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

## 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

Highlighted
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

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?