Calculating a monthly variance with a twist

DAX DAX Calculations
1

Hi

Data has multiple companies production data for a month, this includes a column for actuals and a column for forecast. The forecast column has a vallue for every day of the month, the actuals depends on where in the month we are up to. It is possibile that companies have data up to different actual dates.

We group our companies into pods (i.e. 2-4 companies in each pod). What I would like to do is to calulate the POD varaince to forecast, butstruggling with the different date thing.

image

POD 1 should retun 1.89% and POD 2 -5.11%

any suggestions, I tried Sumx and a normal calculate but have struggled to get ar oound the different dates issues, i.e. it keeps pulling the forecast for the max date of the pod not treating each company as an individual.

thanks for any hlep

2

@farmboy,

Here you go:

image
image734×207 8.63 KB

The key here is the use of ALLEXCEPT to remove all filters except for Pod:

Total Actual by Pod =

CALCULATE(
    SUM( Data[Actual] ),
    ALLEXCEPT(
        Data,
        Data[POD]
    )
)

I hope this is helpful. Full solution file attached below.

3

thanks for that, had never seen that before. How do i get this to work with other filters i.e. currently I calculate the company forecast values using the following

Cur_Mth_Fcst = CALCULATE(sum(bud_milk_production[daily_kgms]),

FILTER(all( date_table), date_table[Date] <= [cur_mth_date] && date_table[month_name] = [curr_mth]&&

date_table[Financial Year] = [cur_finc_year] ) ,Filter(all(‘Budget Version’),‘Budget Version’[budget_version]=[selected_fcst_version]))

4

@farmboy,

You can just add it as another filter condition in your CALCULATE statement. I’ve included a couple of links below with videos and other information that will help you apply this correctly in your specific situations. If you have any problems, just open another thread on the forum with the specifics.

Cur_Mth_Fcst =
CALCULATE(
    sum( bud_milk_production[daily_kgms] ),
    FILTER(
        all( date_table ),
        date_table[Date] <= [cur_mth_date] &&
        date_table[month_name] = [curr_mth] &&
        date_table[Financial Year] = [cur_finc_year]
    ),
    Filter(
        all( ‘ Budget Version ’ ),
        ‘ Budget Version ’ [budget_version] = [selected_fcst_version]
    ),
    ALLEXCEPT( Table, Table[Field1], Table[Field2] )
)

https://info.enterprisedna.co/dax-function-guide/allexcept/

I hope this is helpful.

– Brian