Forum Discussion
Extract Value from previous date
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?
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])
i am not sure why you need those date values from 1-Jan-19 till 2024 using CALENDAR() when you mostly dont have value in those dates (these values make the resource error before).
Just SUMMARIZE those date for simplify and reduce pbix load.2. in 'Report Mth_Key', create a new calculated column to define previous date.Previous Date =
MAXX(
FILTER(
'Report Mth_Key',
'Report Mth_Key'[Report Month]<EARLIER('Report Mth_Key'[Report Month])
),
'Report Mth_Key'[Report Month]
)3. after your change 'Report Mth_Key', you need to redefine the relationship. I made the exact same relationship as you have before.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.
11 Replies
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]Could you see if this box item can be downloaded? I have attached the link below.😀
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]
)
)same as above, if you want to do in measure, create a new measure with following DAX.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]
)
)Hope this will help.Thank you.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.
