Multiple filters in table with relative Date and Count

[samc_26]

Hi so I've been trying to work out how to add a measure to a table which will ignore the 'Month' filter I also have on the page as I want it to always show the last 12 months of results rather than just the selected month (which filters the other coulmns) I've managed to do it for another one of the columns where it just does a count on the last 3 months of data but I want this one to count 12 months to only include how many there are at Level 3. This is a version of how my data looks:

Location	Date	Level
England	01/04/2025	3
England	01/01/2025	3
Scotland	01/06/2025	2
Ireland	01/03/2024	3
Wales	01/08/2025	3

The desired output I want is as below which only counts England and Wales as they are within the last 12 months and at Level 3

Month (filter)

September 2025

Table result: 

Location	Count
England	2
Wales	1

If anyone has any ideas I would be very grateful! So far I can only get the count to come out for the month selected on the page filter which is no use as mentioned ☹️ Thank you for reading! 

[V-yubandi-msft · Accepted solution]

Hi @samc_26 ,

Thank you for contacting the fabric community. I have reviewed your sample data and expected output, and everything looks correct. Your objective is to count Level 3 entries per location over the past 12 months, based on a chosen reference month such as September 2025, while excluding the 'Month' slicer on the page. Your measure accomplishes this as intended.
FYI:

Therefore, your expected output matches what the measure produces. If you are using a fixed reference like DATE(2025, 9, 1) or a value from a disconnected slicer, and have included REMOVEFILTERS(Data[Month]), your approach is accurate and meets the requirements.

I have attached the PBIX file for your reference. Please review it and let me know if you need any adjustments.

Thank you @Selva-Salimi for your quick response.

Regards,
Yugandhar.

[samc_26]

Hi @V-yubandi-msft thank you for the file, mine still isn't working properly but I think it may be a problem with one of my relationships as the table links to a main 'locations' table so that the rest of my data can all align. I'll keep trying!

[Selva-Salimi]

Hi @samc_26 ,

you can write a measure as follows:

measure level_count := calculate(countrows('your_table') , filter (all('your_table') , 'your_table' [level] = 3 && 'your_table[date] >= EDATE(selectedvalue('your_table'[date]), -12) && 'your_table'[date]<selectedvalue('your_table'[date])

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[samc_26]

Hi, thank you for the dax, unfortunately when I get to the bit referencing the table again after the first && it won't let me select any table, only a measure, not sure how to fix this? 

[Selva-Salimi]

@samc_26 would you please share how you write the measure? Dont you have any missing parentheses??

[samc_26]

Hi @Selva-Salimi ok so I managed to get the formula to run but it doesn't show any numbers in my table which I think is because my month filter on the page is using a seperate date table. When I try to replace  the [Date] from the formula and replace it with the column the month is coming from in my date table it says it can't find the table, this is the formula that works: 

measure count =

CALCULATE(COUNTROWS(Incidents3),

    FILTER(ALL(Incidents3), Incidents3[Tier] = 3

        && 'Incidents3'[Date] >= EDATE(SELECTEDVALUE(Incidents3[Date]), -12)

        && 'Incidents3'[Date]<SELECTEDVALUE(Incidents3[Date])))

I think I need to change Incidents3[Date] for DateTable[MonthShortYear] but not sure if this is this simple as it won't let me swap them ☹️

[Selva-Salimi]

@samc_26 ,

try this instead, let me know if it works

measure count =

CALCULATE(COUNTROWS(Incidents3), Filter (incidents3, Incidents3[Tier] = 3),

   FILTER(ALL(DateTable), DateTable[MonthShortYear] >= EDATE(SELECTEDVALUE(DateTable[MonthShortYear]), -12)  && DateTable[MonthShortYear]<SELECTEDVALUE(DateTable[MonthShortYear])))

**if monthShortYear is not in date format, use the column in your date table which has the date format instead.

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[samc_26]

Hi @Selva-Salimi thank you again for helping but it still isn't working... I've made a simple version of my report so I can show you how it's structured if this is more useful 🤞 When I use MonthShortYear it breaks the table and when I use the Date column it doesn't show any values ☹️ If you can make any sense of this I would be extremely grateful 👍

[Selva-Salimi]

Hi @samc_26 ,

please change the filter direction between 'incident table' and 'date table' to "single" where the direction is from date table to incidents. and also, why do you use date from incidents table in your visualisation??, remove that if you dont need.

**the second measure 'count monthshort' is not correct, use 'measure count date'

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[samc_26]

Hi @Selva-Salimi it is single direction on my real report and I haven't used the date from the incidents table, it shows the date highlighted from the date table. I know the monthshortyear one doesn't work but neither does the date one. At this point I'm happy to find another workaround but thank you anyway

[Selva-Salimi]

@samc_26 , oh, I know why it does not work!!!, you have to select a date in slicer!

[samc_26]

When I selected a month it only showed the incidents for that month...I think I've got it working now anyway with this, it's doing what I wanted using the original measure [Tier 3] which filters out any not tier 3.

Incidents = CALCULATE([Tier 3], REMOVEFILTERS('*Date_today'[MonthShortYear]))

Thank you anyway, I appreciate your help

[srlabhe]

Hi try below DAX

Count of Months (Last 12) =
VAR Last12Months =
DATESINPERIOD(
'Date'[Date],
MAX('Date'[Date]),
-12,
MONTH
)
RETURN
CALCULATE(
COUNT('Date'[MonthYear]),
Last12Months,
'YourTable'[HierarchyLevel] = "Level 3"
)

[samc_26]

Hi @srlabhe thank you for the suggestion, unfortunately the measure tells me every location has a count of 92 so not sure what's gone wrong 😅