Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
What measure can I use to sum amount for the same month from a previous report, but the previous report is conditional based on an Accounting Period End Date (i.e., report date) and Period End Date (i.e. month). The following example hopefully explains the logic:
Accounting Period End date (a) | Period End date (b) | Logic | Source Accounting Period End date report for Prev Month | Comment |
30 Jun 22 | 30 Apr 22 | b < a | 30 Mar 22 | Last forecast for Apr. May & June (and subsequent) reports contain Apr actuals |
30 Jun 22 | 30 May 22 | b < a | 30 Apr 22 | Last forecast for May. June (and subsequent) reports contain May actuals. |
30 Jun 22 | 30 Jun 22 | b = a | 30 May 22 | Last forecast for Jun. June report contains first June actuals. |
30 Jun 22 | 30 Jul 22 | b > a | 30 May 22 | Last data available. July is future month, forecast can change |
30 Jun 22 | 30 Aug 22 | b > a | 30 May 22 | Last data available. Aug is future month, forecast can change |
The Link to "Previous Month Forecast.pbix" provides dummy Forecast data with 12 reports for a single year, which includes expected amounts. The only other table is Dates (relationship = Dates[Date] 1:* Forecasts[Period End Date]). Users select a single Accounting Period End Date via a slicer and results should be based on this date.
To better illustrate, I've achieved the expected result by adding a calculated column to lookup the previous amount, however this will be unreliable as depends on unique records (duplications will occur if multiple reports are uploaded for the same Accounting Period End Date, which is highly likely), plus I would prefer to use a measure. Notes are also included in the calculated column, e.g.:
The closest I've got is the following measure (but I've hit a brick wall and cannot figure out how to apply the rules for future Period End dates v Accounting Period End dates in a measure):
A page is included in the pbix file with the Accounting Period End date slicer and a simple pivot table to show results. The following is based on a selected Accounting Period End date of 30th June 2022:
Month | Forecast | Actual | Total Forecast | Expected Prev Total Forecast | Prev Total Forecast (Calc Col) | Prev Total Forecast (Measure) |
Jan | £1 | £1 | £0 | |||
Feb | £202 | £202 | £2 | £2 | £2 | |
Mar | £303 | £303 | £203 | £203 | £203 | |
Apr | £404 | £404 | £304 | £304 | £304 | |
May | £505 | £505 | £405 | £405 | £405 | |
Jun | £606 | £606 | £506 | £506 | £506 | |
Jul | £607 | £607 | £507 | £507 | £607 | |
Aug | £608 | £608 | £508 | £508 | £708 | |
Sep | £609 | £609 | £509 | £509 | £809 | |
Oct | £610 | £610 | £510 | £510 | £910 | |
Nov | £611 | £611 | £511 | £511 | £1,011 | |
Dec | £612 | £612 | £512 | £512 | £1,112 |
Any help or steer in the right direction would be greatly appreciated.
Thanks 🙂
@Tooley , It is always better to use date table for time intelligence
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
Hi amitchandak,
Thanks for responding and making the point about using time intelligence on the date table.
The DAX examples you kindly provided don't solve the problem, and I couldn't see one in the training video links.
Apologies if I didn't explain clearer in my original post. What I need is not strictly the value from last months report, but a value from a prior report relative to both the "Accounting Period End date" and "Period End date" selected. The followingpoints and supporting picture will hopefully help better explain (note, all values are for the June month).
If I had the following table in Excel:
The Excel formula in the "Prev Value...." column might be:
How can this be achieved in a DAX measure?
Thanks
Tooley
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |