Forum Discussion
Extract Value from previous date
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.
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.
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.
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!