Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |