Forum Discussion

mmarshalek's avatar
mmarshalek
Frequent Visitor
9 years ago

Calculation of 2 values different dates

I've been working on this for about 5 days now,...  and really stuck...  Could use help...  I've attached my Power BI table..

 

Trying to subtract "ForecastCore3" from CM_Ship Actuals...

Need to use Current Month for "CM_Ship_Actuals" and current month - 3 for "ForecastCore3"

 

Is this possible in DAX?

 

Thanks,

Mike

 

 

Need Help

6 Replies

  • dearwatson's avatar
    dearwatson
    Continued Contributor
    9 years ago

    Parallel Period should be your friend here:

     

    You need contigeous dates for this to work.. if you dont have running dates use a calendar table :)

     

    Forecast Current = SUM(Table1[Forecast])

    Forecast -3 = CALCULATE([Forecast Current],PARALLELPERIOD(Table1[Date],-3,MONTH))

     

  • mmarshalek's avatar
    mmarshalek
    Frequent Visitor
    9 years ago

    Thank you! I've tried what you suggested however still now lining correctly....  I had created a calendar table and no success..  Below is the output from my adjustments and I have also include more detailed..

     

     

    More detail:

    Thanks again!

    • v-haibl-msft's avatar
      v-haibl-msft
      Microsoft Employee
      9 years ago

      mmarshalek

       

      Please try to create a calcuated column with following formula.

       

      MAPE =
VAR ValueThreeMonthAgo =
    LOOKUPVALUE (
        Table2[ForcastCore3],
        Table2[RecordDate], EDATE ( Table2[RecordDate], -3 ),
        Table2[Businessline], Table2[Businessline]
    )
RETURN
    IF (
        ISBLANK ( ValueThreeMonthAgo ),
        BLANK (),
        ABS ( Table2[CM_Orders_Actual] - ValueThreeMonthAgo )
            / Table2[CM_Orders_Actual]
    )

       

      Best Regards,
      Herbert

      • mmarshalek's avatar
        mmarshalek
        Frequent Visitor
        9 years ago

        Hi Herbert,

         

        Still having issues...  I'm sure is something simple but just cannot figure it out...  It does not like ProductLine or RecordDate...