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...
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

     

     

     

     

     

     

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.
  • jamuka's avatar
    jamuka
    Helper IV
    8 months ago

    Dear Ahmed-Elfeel 

    both measures returned wrong results.