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.
Hi
I have a facts table with 4 columns job ref, completed date, Contactor and Spend. The completed dates start Apr 2021 and will eventually end 30th Mar 2022 (our financial year)
Facts Table
Job Ref | Completed Date | Contractor | Spend |
154114 | 01/10/2021 | SCP | £1,695 |
136631 | 02/09/2021 | DLO | £539 |
185388 | 03/10/2021 | DLO | £1,005 |
179287 | 04/08/2021 | SCP | £994 |
208108 | 04/08/2021 | DLO | £677 |
203766 | 06/07/2021 | DLO | £452 |
197964 | 07/07/2021 | DLO | £306 |
207726 | 08/06/2021 | SCP | £1,849 |
147365 | 09/05/2021 | SCP | £1,667 |
139481 | 10/04/2021 | SCP | £1,807 |
152219 | 10/04/2021 | SCP | £1,665 |
133598 | 10/04/2021 | SCP | £448 |
I would like to create a forcast DAX measure for all incomplete or partially completed months. Although today is 11th October I would not include October into the forcast as October is not yet fininshed. I would also like the DAX forcast measure to be based on all previously completed months of our financial year. So in this example if would be Apr, May, Jun, Jul, Aug and Sept = 6 months of historical data. Then on 1st November we will have a completed October month so the forcast measure would then need to look at 7 months of data and so on.
My final Matrix will look something like this
Status | Month | DLO | SCP |
Actual | Apr | £104 | £75,317 |
Actual | May | £2,238 | £63,778 |
Actual | Jun | £13,876 | £57,768 |
Actual | Jul | £9,248 | £47,897 |
Actual | Aug | £11,501 | £30,356 |
Actual | Sep | £9,677 | £5,500 |
Forecast | Oct | £7,774 | £46,769 |
Forecast | Nov | £7,774 | £46,769 |
Forecast | Dec | £7,774 | £46,769 |
Forecast | Jan | £7,774 | £46,769 |
Forecast | Feb | £7,774 | £46,769 |
Forecast | Mar | £7,774 | £46,769 |
Hopefully I have explained this ok.
thank you
Richard
Hi
Just wondering if there an update to my response. best regards Richard
Hi Amitchandak thank you for your quick response.
The DAX funtion provided required a small amount of editing to sort of work However it provide the total amount. Where as I need the average for the 6 months. Also I require it to be dynamic , so that once October 2021 month has past. The -6 changes to -7 and once November 2021 has past the -7 changes to -8.
Code provided
My amended code
Month | Spend | RIchardsComments |
Apr | 28183 | Actual Spend |
May | 31523 | Actual Spend |
Jun | 4096 | Actual Spend |
Jul | 43831 | Actual Spend |
Aug | 33873 | Actual Spend |
Sep | 27275 | Actual Spend |
Oct | 28130 | Forcast spend = Average last 6 months |
Nov | 28130 | Forcast spend = Average last 6 months |
Dec | 28130 | Forcast spend = Average last 6 months |
Jan | 28130 | Forcast spend = Average last 6 months |
Feb | 28130 | Forcast spend = Average last 6 months |
Mar | 28130 | Forcast spend = Average last 6 months |
Month | Spend | RIchardsComments |
Apr | 28183 | Actual Spend |
May | 31523 | Actual Spend |
Jun | 4096 | Actual Spend |
Jul | 43831 | Actual Spend |
Aug | 33873 | Actual Spend |
Sep | 27275 | Actual Spend |
Oct | 41231 | Actual Spend |
Nov | 30002 | Forcast spend = Average last 7 months |
Dec | 30002 | Forcast spend = Average last 7 months |
Jan | 30002 | Forcast spend = Average last 7 months |
Feb | 30002 | Forcast spend = Average last 7 months |
Mar | 30002 | Forcast spend = Average last 7 months |
Many thanks
Richard
@cottrera , Assuming month is coming from a date table
new DLO = if( max('Date'[Date]) <=eomonth(today(),-1), [DLO] CALCULATE([DLO],DATESINPERIOD('Date'[Date ],eomonth(today(),-1),-6,MONTH)) )
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |