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.

Yuiitsu's avatar
Yuiitsu
Helper V
2 years ago

kushanNa 

 

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

Demo File_1 

 

kushanNa's avatar
kushanNa
Super User
2 years ago

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

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