Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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% |
Solved! Go to Solution.
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?
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))
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
the syntax I posted is for calculated column, were you looking for a measure syntax?
I did apply this as a column but I am looking for a measure yes, sorry I should have stated this originally
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)
I tried that and it still returns blanks. I cannot work out why it is not working?!
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
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
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?
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?
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |