Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi All
I am currently trying to create a dashboard to show difference between the current and previous forecast.
The simple sample of end result should look like this:
Date | Current Forecast | Previous Forecast | Difference |
2024 June 12 | 24299906978 | - | 24299906978 |
2024 June 21 | 27994430477 | 24299906978 | 3694523499 |
2024 July 10 | 30211644827 | 27994430477 | 2217214350 |
2024 July 23 | 30911655818 | 30211644827 | 700010991 |
1st I want to get the previous forecast value, the measure I am using is this but it is not giving me the result I want.
Previous BK = CALCULATE(
SUM('HQ Booking File'[Current HQ Booking Amount]),
PREVIOUSDAY('Report Mth_Key'[Date]))
Date | Current Forecast | Previous Forecast |
2024 June 12 | 24299906978 | |
2025 June 13 | 24299906978 | |
2024 June 21 | 27994430477 | |
2024 June 22 | 27994430477 | |
2024 July 10 | 30211644827 | |
2024 July 11 | 30211644827 | |
2024 July 23 | 30911655818 | |
2024 July 24 | 30911655818 |
Also, I need to let the measure be filterable by customer and product. Can anyone help to correct my measure?
Solved! Go to Solution.
Hello @Yuiitsu
honestly, your table is very confusing (not sure what they are for but too many date values).
however, here is another perspective beside of what @kushanNa's solution.
1. change your 'Report Mth_Key' with this DAX
Report Mth_Key = SUMMARIZE('HQ Booking File','HQ Booking File'[Report Month])
Previous Date =
MAXX(
FILTER(
'Report Mth_Key',
'Report Mth_Key'[Report Month]<EARLIER('Report Mth_Key'[Report Month])
),
'Report Mth_Key'[Report Month]
)
4. create two new measures with following DAX for calculating previous forecast and difference. then plot those two measures into your table visual.
Previous Forecast =
var _Date = SELECTEDVALUE('Report Mth_Key'[Previous Date])
Return
CALCULATE(
[Current Forecast],
'Report Mth_Key'[Report Month]=_Date
)
Difference = [Current Forecast]-[Previous Forecast]
5. Change your slicer value from 'HQ Booking File' to 'Report Mth_Key' since date value in 'Report Mth_Key' is used in measures.
Hope this will help.
Thank you.
Hi, this is bit of a long shot without knowing how your tables and relationships looks like
try to do this
create a calculated table
NewForecastTable2 =
SUMMARIZE(
FILTER(
'HQ Booking File',
'HQ Booking File'[Date] IN VALUES('Report Mth_Key'[Date])
),
'HQ Booking File'[Date],
"Current Forecast", SUM('HQ Booking File'[Current HQ Booking Amount])
)
and then create a previous forecast calculated column in it
Previous Forecast =
VAR CurrentDate = NewForecastTable2[Date]
RETURN
CALCULATE(
MAX(NewForecastTable[Current Forecast]),
FILTER(
NewForecastTable,
NewForecastTable[Date] < CurrentDate
)
)
and next create a difference calculated column
diff = NewForecastTable2[Current Forecast] - NewForecastTable2[Previous Forecast]
@kushanNa Thank you! This actually works!
Only thing is I cannot filter by customer or product because this new table doesnt have that information.
Currently I am trying to create a sample file in my personal onedrive to link it here.
It might be easier with a sample data.
Could you see if this box item can be downloaded? I have attached the link below.😀
update the table with this code and see if it works for you ?
NewForecastTable2 =
SUMMARIZE(
FILTER(
'HQ Booking File',
'HQ Booking File'[Report Month] IN VALUES('Report Mth_Key'[Date])
),
'HQ Booking File'[Report Month],
'HQ Booking File'[Customer Name],
"Current Forecast", SUM('HQ Booking File'[Current HQ Booking Amount])
)
Hi @kushanNa
With this new code I am able to include the filter for customer but the previous forecast result is not correct. I think amendment has to be made there as well.
Could you guide me a little more here?
Previous Forecast =
VAR CurrentDate = 'NewForecastTable2'[Report Month]
RETURN
CALCULATE(
MAX('NewForecastTable2'[Current Forecast]),
FILTER(
'NewForecastTable2',
'NewForecastTable2'[Report Month] < CurrentDate
)
)
hello @Yuiitsu
please check if this accomodate your need.
create a new calculated column with following DAX
Previous Forecast MAXX =
MAXX(
FILTER(
'Table',
'Table'[Date]<EARLIER('Table'[Date])
),
'Table'[Current Forecast]
)
or if you want to do with measure, then create a new measure with following DAX:
Previous Forecast =
var _Date = SELECTEDVALUE('Table'[Date])
var _Current = SELECTEDVALUE('Table'[Current Forecast])
Return
MAXX(
FILTER(
ALLSELECTED('Table'),
'Table'[Date]<_Date
),
'Table'[Current Forecast]
)
Difference Measure =
var _Current = SELECTEDVALUE('Table'[Current Forecast])
Return
_Current-'Table'[Previous Forecast]
for the next table in calculated column, simple if statement addition should do the trick (i assumed the second line is 13-Jun-24 not 13-Jun-25 since the value is from 12-Jun-24).
Previous Forecast =
IF(
ISBLANK('Table 2'[Current Forecast]),
MAXX(
FILTER(
'Table 2',
'Table 2'[Date]<EARLIER('Table 2'[Date])
),
'Table 2'[Current Forecast]
)
)
Previous Forecast 2 =
var _Date = SELECTEDVALUE('Table 2'[Date])
var _Current = SELECTEDVALUE('Table 2'[Current Forecast])
Return
IF(
ISBLANK(_Current),
MAXX(
FILTER(
ALLSELECTED('Table 2'),
'Table 2'[Date]<_Date
),
'Table 2'[Current Forecast]
)
)
Hi thanks for your reply!
I am very sorry for not explaining clearly.
the 2nd table is the wrong output result from my measure. Therefore you can ignore it. I was trying to show what went wrong in my measure.
Previous BK = CALCULATE(
SUM('HQ Booking File'[Current HQ Booking Amount]),
PREVIOUSDAY('Report Mth_Key'[Date]))
This is the correct result I want.
Date | Current Forecast | Previous Forecast | Difference |
2024 June 12 | 24299906978 | - | 24299906978 |
2024 June 21 | 27994430477 | 24299906978 | 3694523499 |
2024 July 10 | 30211644827 | 27994430477 | 2217214350 |
2024 July 23 | 30911655818 | 30211644827 | 700010991 |
I've tried your method but it doesnt work for me. Afew things I could have explained better:
1. The date column and the fact are not in the same table. I have a seperate Date key table for dates because my original pbix has multiple facts table which I linked up in star schema.
Should I use the date column inside the facts table instead?
2. The column "Current Forecast" is the result from the calculated SUM of the total booking amount column in my fact table -> SUM('HQ Booking File'[Current HQ Booking Amount])
3. The column " Previous Forecast" and "Difference" are the columns I am trying to get.
I tried to amend your measure but the result says that it has exceeded the available resources.
Previous Forecast =
var _Date = SELECTEDVALUE('Report Mth_Key'[Date])
var _Current = SELECTEDVALUE('HQ Booking File'[Current HQ Booking Amount])
Return
MAXX(
FILTER(
ALLSELECTED('Report Mth_Key'),
'Report Mth_Key'[Date]<_Date
),
SUM('HQ Booking File'[Current HQ Booking Amount])
)
I also tried to use your calculated column method but an error appear. Probably because the dates are not unique?
Previous Forecast MAXX =
MAXX(
FILTER(
'Report Mth_Key',
'Report Mth_Key'[Date]<EARLIER('Report Mth_Key'[Date])
),
SUM('HQ Booking File'[Current HQ Booking Amount]
)
Can you tell me what is wrong with my amendment?
hello @Yuiitsu
i think you should have Date column in your fact_tbl otherwise how you can tell what are those values in 'Current Forecast'.
If you want to get sum value, just use SUMX instead of MAXX. Then use Current HQ Booking Amount in SUMX expression.
Previous Forecast MAXX =
SUMX(
FILTER(
'Report Mth_Key',
'Report Mth_Key'[Date]<EARLIER('Report Mth_Key'[Date])
),
'HQ Booking File'[Current HQ Booking Amount]
)
Try to use Date value correlated with value you want to be SUM-ed (not your Date dimension tabl)
Also for your visual resource error, i think there is a limitation invisual when you put a measure with huge data. I got that same error so i used column when it happened.
If you still have the problem, please consider to share your sample data (remove confidential data).
Hope this will help.
Thank you.
@Irwan Hi!
I have attached the link below to box. Could you see if you can download the sample file?
Hello @Yuiitsu
honestly, your table is very confusing (not sure what they are for but too many date values).
however, here is another perspective beside of what @kushanNa's solution.
1. change your 'Report Mth_Key' with this DAX
Report Mth_Key = SUMMARIZE('HQ Booking File','HQ Booking File'[Report Month])
Previous Date =
MAXX(
FILTER(
'Report Mth_Key',
'Report Mth_Key'[Report Month]<EARLIER('Report Mth_Key'[Report Month])
),
'Report Mth_Key'[Report Month]
)
4. create two new measures with following DAX for calculating previous forecast and difference. then plot those two measures into your table visual.
Previous Forecast =
var _Date = SELECTEDVALUE('Report Mth_Key'[Previous Date])
Return
CALCULATE(
[Current Forecast],
'Report Mth_Key'[Report Month]=_Date
)
Difference = [Current Forecast]-[Previous Forecast]
5. Change your slicer value from 'HQ Booking File' to 'Report Mth_Key' since date value in 'Report Mth_Key' is used in measures.
Hope this will help.
Thank you.
@Irwan Thank you for your input.
As a beginner in Pbix, I believe I have alot more to improve. Thank you for your comment.
Regarding date values from 1-Jan-19 till 2024 using CALENDAR(), the original fact data contains reports dated in 2019 till current.
For demo purpose i have only kept the latest 4 months and removed most of the data.
I will take your method and apply in my raw file.
Once again thank you for your help!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
127 | |
108 | |
87 | |
70 | |
66 |