Filter a visual by last month

[fbittencourt]

I need to filter a visual by last month, my model does not have a date table. I need to apply a mesure or a calculated column to filter

My month[Mois] data contains 1 to 13 I need to not include 13 month as last when applied

I tried this mesure but how can I apply it?

Mois max = CALCULATE(MAX(FAIT_INDICATEURS[MOIS]),FILTER(FAIT_INDICATEURS,FAIT_INDICATEURS[MOIS]

    ))

This mesure is apllied on the values 

Nb_Personnes = CALCULATE (

DISTINCTCOUNT ( REF_RESSOURCE[UID ressource] ),

FILTER(REF_RESSOURCE,REF_RESSOURCE[Rôle] <> "GEN" &&FAIT_INDICATEURS[Charge HTP]<>0

[DataNinja777]

Hi @fbittencourt ,

To filter a visual by the last month in your dataset without using a date table, you can achieve this by creating a DAX measure that dynamically calculates the latest month, ensuring that month 13 is excluded from being considered as the last month.

Start by creating a measure to determine the latest month from your [MOIS] column, considering only values from 1 to 12. The following DAX measure will calculate the highest month number, excluding 13:

LastMonth = 
VAR MaxMois = MAXX(FILTER(FAIT_INDICATEURS, FAIT_INDICATEURS[MOIS] <= 12), FAIT_INDICATEURS[MOIS])
RETURN MaxMois

Next, incorporate this measure into your existing Nb_Personnes measure to filter the visual by the last month. Here’s the updated measure that filters by the calculated LastMonth value:

Nb_Personnes = 
VAR LastMonthValue = 
    MAXX(FILTER(FAIT_INDICATEURS, FAIT_INDICATEURS[MOIS] <= 12), FAIT_INDICATEURS[MOIS])
RETURN
CALCULATE(
    DISTINCTCOUNT(REF_RESSOURCE[UID ressource]),
    FAIT_INDICATEURS[MOIS] = LastMonthValue,
    REF_RESSOURCE[Rôle] <> "GEN",
    FAIT_INDICATEURS[Charge HTP] <> 0
)

This measure calculates the number of unique resources for the last month by dynamically determining the maximum month value that is 12 or less. The CALCULATE function applies this filter alongside the conditions to exclude certain roles and ensure that only rows with non-zero values in the Charge HTP column are counted.

If you prefer to apply the last month filter directly within the visual without modifying the existing measure, you can also add a filter on the [MOIS] column in the Filters pane. Set it to show the top 1 value by [MOIS] and apply an additional condition to exclude month 13 by setting the filter to [MOIS] <= 12.

These steps will ensure that your visual always reflects data from the most recent month in your dataset, excluding any values from the 13th month.

Best regards,

[fbittencourt · Accepted solution]

Using the filter works properly thank for your quick answer.

The combination of the mesures has an error so I applied the first option