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 

 

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

Irwan Hi!

 

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

Demo File_1 

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