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... Nee...
Need Help
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...

       

       

      • mattbrice's avatar
        mattbrice
        Solution Sage
        9 years ago

        What are you putting on the rows?   Just the Date like shown?  If so how are you narrowing down the values to a particular product? Slicing by product number?

         

        The measure should be quite straightforward:

         

        MAPE =
VAR CM_Ship_Actuals =
    SUM ( Table[CM_Ship_Actuals] )
VAR ForecastCore3MoAgo =
    CALCULATE (
        SUM ( Table[ForecastCore3] ),
        PARALLELPERIOD ( Calendar[Date], -3, MONTH )
    )
RETURN
    DIVIDE ( CM_Ship_Actuals - ForecastCore3MoAgo, CM_Ship_Actuals )

         But it depends on what you have in row/column/slicers or other filters...