Ordering Detail Level Table by Total Count of Rows per Location

[nicholas_goodye]

The data:
LocationTable

LocationID	LocationName
001	A
002	B
003	C

IncidentTable

LocationID	IncidentID	Date
003	1	10/1/25
003	2	7/1/25
003	3	10/15/25
001	4	10/1/25
002	5	10/1/25
002	6	10/10/25
002	7	7/1/25
002	8	10/15/25

Assuming the month is currently November, I am looking to be able to pull a list of Incidents from the previous month, ordered by detail the Location that has the most Incidents first. I want to see Location B show up first in my list, as it has 3 Incidents that meet my critera, then C with two instances, then A with 1. The resulting table in PowerBI should look like this:

LocationTable[LocationName]	IncidentTable[IncidentID]	IncidentTable[Date]
B	5	10/1/25
B	6	10/10/25
B	8	10/15/25
C	1	10/1/25
C	3	10/15/25
A	4	10/1/25

Currently, I am pulling this data only ordered by IncidentID. I am also using an "IsLastMonth" measure that is being used because for sending out a version of this report via Paginated Report. This can be removed if the solution to my problem here can bring in only last month's data via some filter, and not a hard coded method that happens in the background while using Power BI.

IsLastMonth =

VAR TodayDate = TODAY()

VAR StartOfThisMonth = DATE(YEAR(TodayDate), MONTH(TodayDate), 1)

VAR StartOfLastMonth = EDATE(StartOfThisMonth, -1)

VAR EndOfLastMonth = EOMONTH(StartOfLastMonth, 0)  

RETURN

IF (

    CALCULATE(

        COUNTROWS('IncidentTable'),

        'IncidentTable'[Date] >= StartOfLastMonth,

        'IncidentTable'[Date] < StartOfThisMonth   // less than first day of this month

    ) > 0,

    1,

    0

)

Hope this is clear enough of a question, thank you!

[v-hashadapu]

Hi @nicholas_goodye , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.

[v-hashadapu]

Hi @nicholas_goodye , Thank you for reaching out to the Microsoft Community Forum.

We find the answer shared by @dinesh_7780  is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.

Thank you @dinesh_7780  for your valuable response.

[dinesh_7780]

Hi @nicholas_goodye ,

Try below steps.

1. Create a Last Month Filter measure.

IsLastMonth =

VAR TodayDate = TODAY()

VAR StartOfThisMonth = DATE(YEAR(TodayDate), MONTH(TodayDate), 1)

VAR StartOfLastMonth = EDATE(StartOfThisMonth, -1)

VAR EndOfLastMonth = EOMONTH(StartOfLastMonth, 0)

RETURN

'IncidentTable'[Date] >= StartOfLastMonth &&

'IncidentTable'[Date] <= EndOfLastMonth

Note: Use this as a visual filter = TRUE.

2. Create a measure that counts incidents per location (last month).

LastMonth Incident Count =

CALCULATE(

    COUNTROWS('IncidentTable'),

    KEEPFILTERS([IsLastMonth])

)

3. Create a measure to be used for sorting the rows. This measure returns the count per location and repeats it on every row, allowing sorting.

Sort By Incident Count =

CALCULATE(

    [LastMonth Incident Count],

    ALLEXCEPT('IncidentTable', 'IncidentTable'[LocationID])

)

Note: This is the key for ordering your detail table.

4.  In your table visual containing

LocationName, IncidentID, Date.

Sort by --> Sort By Incident Count --> Descending

 Sort by --> IncidentTable[Date] --> Ascending .

If my response as resolved your issue please mark it as solution and give kudos.

Thanks,

Dinesh

[Praful_Potphode]

Hi @nicholas_goodye 

i created one measure to show 1 or 0 for previous month as shown below.

previous month = 
IF(
 year([Today's Date])=YEAR(MAX(DimDate[Date])) && MONTH([Today's Date])-1=MONTH(MAX(IncidentTable[Date])),1
 ,0
 )

Today's Date = TODAY()

Today's Date is also a measure which you can customise.if you want month from slicer use SELECTEDVALUE function.

Sample PBIX can be downloade from here.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[Zanqueta · Accepted solution]

Hello @nicholas_goodye ,

I tested the function bellow and worked here;

CountOfIncidents_LastMonth = 
VAR StartOfLastMonth = EDATE(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), -1) 
VAR StartOfThisMonth = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
RETURN
    CALCULATE(
        COUNTROWS('IncidentTable'),
        'IncidentTable'[Date] >= StartOfLastMonth,
        'IncidentTable'[Date] < StartOfThisMonth
    )

✅ If this answer solves your problem, please mark it as correct to help other community members.