Difference between two table values with different dates

[jamuka]

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 '25	Apr '25
Jan '25	7	22	75
Feb '25		34	86	77
Mar '25			81	73
Apr '25				73

Current Result

Year Month	Jan '25	Feb '25	Mar '25	Apr '25
Jan '25	7	178
Feb '25		34		168
Mar '25			81	172
Apr '25				73

and file

[Praful_Potphode · Accepted solution]

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]

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]
)

[Ahmed-Elfeel]

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.

[jamuka]

Dear @Ahmed-Elfeel 

both measures returned wrong results. 

  

[Ashish_Mathur]

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]

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