Forum Discussion

nicholas_goodye's avatar
nicholas_goodye
Frequent Visitor
11 months ago
Solved

Ordering Detail Level Table by Total Count of Rows per Location

The data:
LocationTable

LocationID  LocationName
001

A

002

B

003

C


IncidentTable

LocationID  IncidentID   Date
003110/1/25
00327/1/25
003310/15/25
001410/1/25

002

5

10/1/25
002610/10/25
00277/1/25
002810/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]  
B510/1/25
B610/10/25
B810/15/25
C110/1/25
C310/15/25
A410/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!

  • Zanqueta's avatar
    Zanqueta
    11 months ago

    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.
     

5 Replies

  • Zanqueta's avatar
    Zanqueta
    Icon for Super User rankSuper User
    11 months ago

    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.
     

  • Anonymous's avatar
    Anonymous
    Not applicable
    11 months ago

    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's avatar
    Praful_Potphode
    Icon for Super User rankSuper User
    11 months ago

    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

     

     

  • v-hashadapu's avatar
    v-hashadapu
    Icon for Community Support rankCommunity Support
    11 months ago

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

     

    We find the answer shared by Anonymous  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 Anonymous  for your valuable response.

  • v-hashadapu's avatar
    v-hashadapu
    Icon for Community Support rankCommunity Support
    11 months ago

    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.