Forum Discussion

Yuiitsu's avatar
Yuiitsu
Helper V
2 years ago
Solved

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...
Need Help
Tutorial Requests
  • Irwan's avatar
    Irwan
    2 years ago

    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's avatar
Irwan
Super User
2 years ago

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's avatar
Yuiitsu
Helper V
2 years ago

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!