Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Yuiitsu
Helper V
Helper V

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:

DateCurrent ForecastPrevious ForecastDifference
2024 June 1224299906978-24299906978
2024 June 2127994430477242999069783694523499
2024 July 1030211644827279944304772217214350
2024 July 23

30911655818

30211644827700010991

 

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

 

 

DateCurrent ForecastPrevious Forecast
2024 June 1224299906978 
2025 June 13 24299906978
2024 June 2127994430477 
2024 June 22 27994430477
2024 July 1030211644827 
2024 July 11 30211644827
2024 July 2330911655818 
2024 July 24 30911655818

 

Also, I need to let the measure be filterable by customer and product. Can anyone help to correct my measure?

 

1 ACCEPTED SOLUTION
Irwan
Memorable Member
Memorable Member

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.

 

Irwan_0-1724202615800.png

 

 

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]
)
Irwan_3-1724203023664.png
 
3. after your change 'Report Mth_Key', you need to redefine the relationship. I made the exact same relationship as you have before.
Irwan_2-1724202954203.png

 

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]
Irwan_4-1724203080761.png

 

5. Change your slicer value from 'HQ Booking File' to 'Report Mth_Key' since date value in 'Report Mth_Key' is used in measures.

Irwan_5-1724203264437.png

 

 

Hope this will help.

Thank you.

View solution in original post

11 REPLIES 11
kushanNa
Frequent Visitor

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_0-1724052841511.png

 

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

@kushanNa 

 

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

Demo File_1 

 

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

 

Yuiitsu_0-1724201858273.png

Irwan
Memorable Member
Memorable Member

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

Irwan_4-1724040184871.png

 

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]
Irwan_0-1724042125073.png

 

 

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

Irwan_2-1724043706002.png


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]
    )
)
Irwan_3-1724043787512.png

 

 
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.

DateCurrent ForecastPrevious ForecastDifference
2024 June 1224299906978-24299906978
2024 June 2127994430477242999069783694523499
2024 July 1030211644827279944304772217214350
2024 July 23

30911655818

30211644827700010991

 

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.

Yuiitsu_0-1724045472409.png

 

 

 

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?

Yuiitsu_1-1724046741983.png

 

 

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
Memorable Member
Memorable Member

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?

Demo File_1 

Irwan
Memorable Member
Memorable Member

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.

 

Irwan_0-1724202615800.png

 

 

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]
)
Irwan_3-1724203023664.png
 
3. after your change 'Report Mth_Key', you need to redefine the relationship. I made the exact same relationship as you have before.
Irwan_2-1724202954203.png

 

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]
Irwan_4-1724203080761.png

 

5. Change your slicer value from 'HQ Booking File' to 'Report Mth_Key' since date value in 'Report Mth_Key' is used in measures.

Irwan_5-1724203264437.png

 

 

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!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.