Forum Discussion

jamuka's avatar
jamuka
Helper IV
8 months ago
Solved

Difference between two table values with different dates

Dear all,

 

I'm trying to calculate my forecast accuracy based on difference between sales and given forecast. we calculating forecast for current month and upcoming two months.

Therefore when I'm calculating October accuracy I'm using forecast from August, September and October with October sales realisation.

 

I have one table for Sales which has a relation with Calendar and one for Forecast, also have a relationship with Calendar.

Forecast table also have a relation with copy of Calendar (named Forecast Calendar) table based on Forecasting Month.

 

I'm trying to create a matrix which will show my accuracy change in time for related month. In my matrix I have Year Month from Calendar in Rows and Year Month from Forecast Calendar in Columns.

But my measure gave an error and I couldn't understand the root of it.

 

below you can see my measure, I also add expected and current matrix result and power bi file.

ABS Error = 
VAR ForecastMonth =
    SELECTEDVALUE ( 'Forecast Calendar'[Year Month] ) 
VAR MonthRealization =
    SELECTEDVALUE ( 'Calendar'[Year Month] ) 
VAR IsTotal =
    ISINSCOPE ( 'Calendar'[Year Month] ) = FALSE () 
RETURN
    IF (
        IsTotal,
        BLANK (),
        IF ( ForecastMonth <= MonthRealization,
            SUMX (
            VALUES ( 'Calendar'[Year Month] ),
            VAR SalesRealization = VAR RowMonth=SELECTEDVALUE('Calendar'[Year Month])
 VAR ColMonth=SELECTEDVALUE('Forecast Calendar'[Year Month])
 RETURN
 IF(RowMonth=ColMonth,
 CALCULATE(SUM('Sales'[Sales])))
                VAR MatchForecast =
                    CALCULATE (
                        SUM ( 'Forecast'[Forecast] ),
                        FILTER (
                            ALL ( 'Forecast Calendar'[Year Month] ),
                            'Forecast Calendar'[Year Month] = ForecastMonth
                        ) )
                VAR Result = ABS ( SalesRealization - MatchForecast ) 
                RETURN
                    Result
            ) ) )

Expected Result

Year Month  Jan '25 Feb '25  Mar '25Apr '25  
Jan '2572275 
Feb '25 348677
Mar '25  8173
Apr '25   73

 

Current Result

 

Year Month  Jan '25  Feb '25  Mar '25  Apr '25  
Jan '257178  
Feb '25 34 168
Mar '25  81172
Apr '25   73

 

and file

Need Help
Show and Tell
  • Praful_Potphode's avatar
    Praful_Potphode
    8 months ago

    Hi jamuka 

    Please try below measures:

    Sales version 1 = 
var selected_calendar_month=MAX('Calendar'[StartofMonth])
var selected_actuals_month=CALCULATE(MAX(Sales[Date]),ALL('Calendar'))
var selected_forecasting_month=CALCULATE(MAX('Forecast'[Forecasting Month]),ALL('Calendar'))
var totalsalesactuals1=CALCULATE([Total Sales],ALL('Calendar'),FILTER(ALL(Sales),Sales[Date]=selected_forecasting_month))
RETURN totalsalesactuals1
    Forecast version 1 = 
var selected_calendar_month=MAX('Calendar'[StartofMonth])
var selected_forecasting_month=CALCULATE(MAX('Forecast'[Forecasting Month]),ALL('Calendar'))
// var totalsalesforecast=SUMX(FILTER(ALL(Forecast),Forecast[Date]=selected_calendar_month && 'Forecast'[Forecasting Month]=selected_forecasting_month),Forecast[Total Forecast])
var totalsalesforecast1=CALCULATE([Total Forecast],ALL('Calendar'),FILTER(ALL(Forecast),Forecast[Date]=selected_calendar_month && 'Forecast'[Forecasting Month]=selected_forecasting_month))
RETURN totalsalesforecast1
    delta version 1 = IF(ISBLANK(Forecast[Forecast version 1]),BLANK(),
[Sales version 1]-Forecast[Forecast version 1]
)

    Below is the screesnhot:

     

    Sample PBIX.

     

    Please give kudos or mark it as solution once confirmed.

     

    Thanks and regards,

    Praful

     

     

     

     

     

     

6 Replies

  • Praful_Potphode's avatar
    Praful_Potphode
    Super User
    8 months ago

    Hi jamuka 

    Please try below measures:

    Sales version 1 = 
var selected_calendar_month=MAX('Calendar'[StartofMonth])
var selected_actuals_month=CALCULATE(MAX(Sales[Date]),ALL('Calendar'))
var selected_forecasting_month=CALCULATE(MAX('Forecast'[Forecasting Month]),ALL('Calendar'))
var totalsalesactuals1=CALCULATE([Total Sales],ALL('Calendar'),FILTER(ALL(Sales),Sales[Date]=selected_forecasting_month))
RETURN totalsalesactuals1
    Forecast version 1 = 
var selected_calendar_month=MAX('Calendar'[StartofMonth])
var selected_forecasting_month=CALCULATE(MAX('Forecast'[Forecasting Month]),ALL('Calendar'))
// var totalsalesforecast=SUMX(FILTER(ALL(Forecast),Forecast[Date]=selected_calendar_month && 'Forecast'[Forecasting Month]=selected_forecasting_month),Forecast[Total Forecast])
var totalsalesforecast1=CALCULATE([Total Forecast],ALL('Calendar'),FILTER(ALL(Forecast),Forecast[Date]=selected_calendar_month && 'Forecast'[Forecasting Month]=selected_forecasting_month))
RETURN totalsalesforecast1
    delta version 1 = IF(ISBLANK(Forecast[Forecast version 1]),BLANK(),
[Sales version 1]-Forecast[Forecast version 1]
)

    Below is the screesnhot:

     

    Sample PBIX.

     

    Please give kudos or mark it as solution once confirmed.

     

    Thanks and regards,

    Praful

     

     

     

     

     

     

    • jamuka's avatar
      jamuka
      Helper IV
      8 months ago

      Dear Praful_Potphode 

       

      thank you for your help. It worked, I made a little update (in Sales version) so there won't be negative values for upcoming monhts.

       

      I'm sharing measure in case someone use.

       

      Sales version 2 = 
VAR selected_calendar_month =
    MAX ( 'Calendar'[StartofMonth] )
VAR selected_actuals_month =
    CALCULATE ( MAX ( Sales[Date] ), ALL ( 'Calendar' ) )
VAR selected_forecasting_month =
    CALCULATE ( 
MAX (
 'Forecast'[Forecasting Month] ), 
ALL ( 'Calendar' ) )
VAR totalsalesactuals1 =
    CALCULATE (
        [Total Sales],
        ALL ( 'Calendar' ),
        FILTER ( 
            ALL ( Sales ), 
            Sales[Date] = selected_forecasting_month
            && Sales[Date] >= selected_calendar_month
        )
    )
VAR Result = totalsalesactuals1
RETURN
    Result

       

       

      Delta version 2= 
IF(
   ISBLANK([Sales version 2]) || ISBLANK([Forecast version 1]),
    BLANK(),
    [Sales version 2] - [Forecast version 1]
)

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 months ago

    Hi,

    In the expected result table, how did youa rive at 22 at the intersection of Jan'25 and Feb'25?  It will be ideal if you can share an MS Excel file with formulas so that i can understand your calculation logic very clearly.

    • jamuka's avatar
      jamuka
      Helper IV
      8 months ago

      Dear Ashish_Mathur 

       

      Please find below my excel tables, I simply subtract related months sales quantity from related months forecast. If there's no sales for related month (because we are forecasting for upcoming two months therefore there will be no sales value for those months) then I do nothing.

      Tables 

       

       

  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    Super User
    8 months ago

    Hi jamuka,

    Here is Corrected Version for your DAX Measure you Can try :

    ABS Error =
VAR CurrentSalesMonth = SELECTEDVALUE('Calendar'[Year Month])
VAR CurrentForecastMonth = SELECTEDVALUE('Forecast Calendar'[Year Month])
VAR IsRowTotal = ISINSCOPE('Calendar'[Year Month]) = FALSE()
VAR IsColTotal = ISINSCOPE('Forecast Calendar'[Year Month]) = FALSE()

RETURN
    IF(
        IsRowTotal || IsColTotal,
        BLANK(),
        IF(
            CurrentForecastMonth <= CurrentSalesMonth,
            VAR SalesAmount = 
                CALCULATE(
                    SUM('Sales'[Sales]),
                    ALL('Forecast Calendar')
                )
            VAR ForecastAmount =
                CALCULATE(
                    SUM('Forecast'[Forecast]),
                    TREATAS({CurrentSalesMonth}, 'Calendar'[Year Month]),
                    ALL('Calendar')
                )
            RETURN
                ABS(SalesAmount - ForecastAmount)
        )
    )

     If the above does not work due to relationship constraints try this bridge table :

    ABS Error =
VAR SalesMonth = SELECTEDVALUE('Calendar'[Year Month])
VAR ForecastMonth = SELECTEDVALUE('Forecast Calendar'[Year Month])

RETURN
    IF(
        NOT ISBLANK(SalesMonth) && NOT ISBLANK(ForecastMonth) && ForecastMonth <= SalesMonth,
        VAR SalesAmount = 
            CALCULATE(SUM('Sales'[Sales]), ALL('Forecast Calendar'))
        VAR ForecastAmount =
            CALCULATE(
                SUM('Forecast'[Forecast]),
                FILTER(
                    ALL('Forecast'),
                    'Forecast'[Forecasting Month] = ForecastMonth &&
                    RELATED('Calendar'[Year Month]) = SalesMonth
                )
            )
        RETURN
            ABS(SalesAmount - ForecastAmount)
    )
    if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.