Filter a visual by last month

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,