Forum Discussion

Yuiitsu's avatar
Yuiitsu
Helper V
1 year 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
    1 year 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.

kushanNa's avatar
kushanNa
Super User
1 year ago

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's avatar
Yuiitsu
Helper V
1 year ago

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's avatar
    Yuiitsu
    Helper V
    1 year 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
      1 year 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
        1 year 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
        )
    )