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

     

     

     

     

     

     

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