Extract Value from previous date

[Yuiitsu]

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?

[kushanNa]

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]

[Yuiitsu]

@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.

[Yuiitsu]

@kushanNa 

Could you see if this box item can be downloaded? I have attached the link below.😀

Demo File_1 

[kushanNa]

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])
)

[Yuiitsu]

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
        )
    )

[Irwan]

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.

[Yuiitsu]

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?

[Irwan]

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.

[Yuiitsu]

@Irwan Hi!

I have attached the link below to box. Could you see if you can download the sample file?

Demo File_1 

[Irwan · Accepted 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])

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.

[Yuiitsu]

@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!