Calculation based on the month and year of the month and year slicer while ignoring the minimum mont

[XangeleZ26]

Hello friends, I want to calculate the amount of SOT between the minimum month and year of the entire dataset (not the minimum value of my months and years slicer) and the maximum month and year that is defined in the months and years slicer . This is a picture from my excel (see SOT as product identifier):

 

This is my current filter:

And this is the DAX I've created (currently it only works if I only filter by years using a years slicer):

añoMAX =
var _max = MAXX(ALLSELECTED(Hoja1[FECHA].[Año]),Hoja1[FECHA].[Año])
return
CALCULATE(
DISTINCTCOUNT(Hoja1[SOT]),
FILTER(
ALL(Hoja1[FECHA].[Año]),
Hoja1[FECHA].[Año] <= _max
))

I tried adding 'Hoja1' [FECHA]. [Mes] to that formula without success.

NOTE: To avoid errors, I only want the month filter to work for the last selected year. For example, if my last SOT has a record of 06/2023, then if I set a SOT limit until 05/2023, the result would be the total number of SOTs from the minimum date to 05/2023, but if the months for other previous years, such as excluding the month of June 2022, this would not affect the result as long as there is a higher year selected, in this case, 2023.

I appreciate your support, thank you very much.

[v-jianboli-msft · Accepted solution]

Hi @XangeleZ26 ,

Please try:

Measure = 
var _a = SELECTCOLUMNS('Table',"FECHA",[FECHA])
var _b = MAXX(_a,[FECHA])
var _c = MINX(ALL('Table'[FECHA]),[FECHA])
return CALCULATE(SUM('Table'[SOT]),FILTER(ALL('Table'),[FECHA]>=_c&&[FECHA]<=_b))

Final output:

Best Regards,

Jianbo Li

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