Calculate correct value for current month and average of next months for non-current months

[ale_BI]

Hi all, I would like to get some help understanding what I''m doing wrong with the formula for a calculated column using DAX:

I should add a calculated column 'cost_real' in which for each row the "real" cost should be calculated as follows:

a. If the date is in the current month, then take cost_prod 
b. If date is not in current month, then take average of next month's cost_prod for that specific product.

The data table has a column named 'dt' for the dates, and the product column is called 'cost_prod'. I need to do this using DAX, and so far I came up with this formula for the first but it doesn't seem to work entirely:

cost_real = 

var current_month = MONTH(TODAY())
var current_year = YEAR(TODAY())
var next_month = IF(current_month = 12, 1, current_month + 1)
var next_year = IF(current_month = 12, current_year + 1, current_year)
var cost_prod =
    CALCULATE(
        AVERAGE(rimanenze_pv_day_nonoil[cost_prod]),
        FILTER(
            rimanenze_pv_day_nonoil,
            YEAR(rimanenze_pv_day_nonoil[dt]) = next_year &&
            MONTH(rimanenze_pv_day_nonoil[dt]) = next_month
        )
    )
var result = 
    IF(
        YEAR(rimanenze_pv_day_nonoil[dt]) = current_year && MONTH(rimanenze_pv_day_nonoil[dt]) = current_month,
        rimanenze_pv_day_nonoil[cost_prod],
        cost_prod
    )

return

Here is also a snapshot of the table as is. There is no model etc, it's only this table here

[v-stephen-msft]

Hi @ale_BI ,

Please replacing rimanenze_pv_day_nonoil[cost_prod] with cost_prod in the last line of the formula. Here is the modified formula:

cost_real = 
VAR current_month = MONTH(TODAY())
VAR current_year = YEAR(TODAY())
VAR next_month = IF(current_month = 12, 1, current_month + 1)
VAR next_year = IF(current_month = 12, current_year + 1, current_year)
VAR cost_prod =
    CALCULATE(
        AVERAGE(rimanenze_pv_day_nonoil[cost_prod]),
        FILTER(
            rimanenze_pv_day_nonoil,
            YEAR(rimanenze_pv_day_nonoil[dt]) = next_year &&
            MONTH(rimanenze_pv_day_nonoil[dt]) = next_month
        )
    )
VAR result = 
    IF(
        YEAR(rimanenze_pv_day_nonoil[dt]) = current_year && MONTH(rimanenze_pv_day_nonoil[dt]) = current_month,
        rimanenze_pv_day_nonoil[cost_prod],
        cost_prod
    )

RETURN
    result

You can use this formula to create a calculated column named cost_real in your table. This column might calculate the correct value for the current month and the average of next months for non-current months.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[ale_BI]

Thank you for the response @v-stephen-msft .

Unfortunately, I am still having issues with this as the column shows blank values for the previous months. I think the issue also has to do with how I create the current_month. So I modified it to this:

cost_real = 
VAR current_month = MONTH(rimanenze_pv_day_nonoil[dt])
VAR current_year = YEAR(rimanenze_pv_day_nonoil[dt])
VAR next_month = IF(current_month = 12, 1, current_month + 1)
VAR next_year = IF(current_month = 12, current_year + 1, current_year)
VAR cost_prod =
    CALCULATE(
        AVERAGE(rimanenze_pv_day_nonoil[cost_prod]),
        FILTER(
            rimanenze_pv_day_nonoil,
            YEAR(rimanenze_pv_day_nonoil[dt]) = next_year &&
            MONTH(rimanenze_pv_day_nonoil[dt]) = next_month
        )
    )
VAR result = 
    IF(
        YEAR(rimanenze_pv_day_nonoil[dt]) = current_year && MONTH(rimanenze_pv_day_nonoil[dt]) = current_month,
        rimanenze_pv_day_nonoil[cost_prod],
        cost_prod
    )

RETURN
    result

While this does show values, it doesn't seem to understand when it should and should not use the ELSE condition of the IF function. Because while before it always took the month from today and added 1, now it takes each month as being the current month by doing row-per-row calculations. For example, if I return just 

YEAR(rimanenze_pv_day_nonoil[dt]) = current_year && MONTH(rimanenze_pv_day_nonoil[dt]) = current_month

 
to show True values, it shows True on any other cell with whatsoever date, because it always reads it as the current date available. Do you have anymore tips on how to proceed, please? This issue is really cursing me. Thanks a lot again.

[some_bih]

Hi @ale_BI I suggest to debug "var cost_prod". Do you get correct / excepect values when you apply this solution as separate measure? If yes, then your issue is part IF (...

Hope this help

[ale_BI]

In facts, the var cost_prod doesn't seem to work because if I only return that I get all blank values, while I would expect to see the averages of the column cost_prod for the next month and year, at each row.