Measure doesn't work with filters

[Regnbagar1]

Hi!

I have created a measure that should calculate the SUM of the last three months. The measure only works for one of the countries when I use a dimension table as filter. In this case the measure works for country 1 but no other country. 

 

 

The relations between the dimension tables are correct (one to many relationships). Does any one know what I'am doing wrong or if there is any other solution to this problem. 

Measure:

Calculation Last 3 month =
VAR calculation_l3m = CALCULATE([VALUE]),
DATESINPERIOD(Date_table[YearMonth], LASTDATE(DATEADD(Date_table[YearMonth], 0, MONTH)), -3, MONTH)
)

RETURN calculation_l3m

Thank you!

[Anonymous · Accepted solution]

Hi @Regnbagar1 ,

Sorry for my late reply.

You could try the following to create a column:

Column = 
var _last3months=EOMONTH([yearmonth],-3)
return CALCULATE(SUM('Table'[Value]),FILTER('Table',[country]=EARLIER('Table'[country]) && [yearmonth]>_last3months &&[yearmonth]<= EARLIER('Table'[yearmonth])))

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

[Regnbagar1]

It worked, thank you so much! 🙂 

[Anonymous]

Hi @Regnbagar1 ,

Mine is a measure. Do you want a measure or a column?

Best Regards,
Eyelyn Qin

[Regnbagar1]

Ah thank you!

Preferably as a calculated column. But 'll try this measure. 🙂

[Anonymous]

Hi @Regnbagar1 ,

Please try:

Sum of Last 3 Months =
VAR _last3months =
    EOMONTH ( MAX ( 'Table'[yearmonth] ), -3 )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [country] = MAX ( 'Table'[country] )
                && [yearmonth] > _last3months
                && [yearmonth] <= MAX ( 'Table'[yearmonth] )
        )
    )

Output:

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

[Regnbagar1]

Hi,

If I try to do exactly as you did I get this result instead.

 

[amitchandak]

@Regnbagar1 , Hope year month is date, prefer use date of date table

Calculation Last 3 month =
VAR calculation_l3m = CALCULATE([VALUE]),
DATESINPERIOD(Date_table[Date], LASTDATE(Date_table[Date]), -3, MONTH)
)

RETURN calculation_l3m

Rolling Months Formula: https://youtu.be/GS5O4G81fww

[Regnbagar1]

Hi,

yearmonth is a date column. And when I use Date_table[Date] I get this error message.

 

[Regnbagar1]

I have the same problem if I try to do it as a calculated column. As mentioned before I would like to sum the value last three months. So that the value in 2021-06 would be 149 + 214,23 + 229 = 592.23