Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
joyc0025
Frequent Visitor

Measure to count a category by month (+ criteria) to visualize in a line chart - not break line

How can I create a DAX measure to display the count of approved orders by month?

  • The filter is Activity Type = “EME” and Decision Code = “Approve”
    The legend is by Business Unit (which is in a related table).
  • The measure only counts for the month range of 12 months prior to the date in the slicer which is in an related table ''Date_Workflow Approvals'[Date]
  • Month’s with no count should have +0 so the chart doesn't show broken lines
  • Ideally any business unit with no count in the filtered date range should be excluded entirely but this is proving difficult and no longer a main priority.
  • The chart needs to have a filter enabled on Decision Code and Activity type so it can be passed via  drill through to a table showing the supporting data.

    Table: Workflow Approvals
    joyc0025_0-1685944631479.png

     

    Table: Purchasing Location
    joyc0025_1-1685944666527.png

     

    Date Table:
    joyc0025_2-1685944694924.png


    Unrelated slicer date table: 'Date_Workflow Approvals'

    joyc0025_3-1685944742457.png


    I think i'm close with the following:
     - but when i put on the chart filter for PO Activity type = "EME"  (for drill through to data), it breaks the lines 

 

Approval EME Count YTD = 
VAR EndMonth =
    MAX ( 'Date_Workflow Approvals'[Date] )
VAR StartMonth =
    EOMONTH ( EndMonth, -12 )
VAR SelectedMonth =
    MAX ( 'Workflow - All Approvals'[Month] )
RETURN
    IF (
        SelectedMonth >= StartMonth
            && SelectedMonth <= EndMonth,
        CALCULATE (
            COUNT ( 'Workflow - All Approvals'[Order Number] ),
            'Workflow - All Approvals'[Decision Code] = "Approve",
            'Workflow - All Approvals'[PO Activity Type] = "EME",
            'Workflow - All Approvals'[Month] >= StartMonth
                && 'Workflow - All Approvals'[Month] <= SelectedMonth
        ) + IF(ISBLANK(SelectedMonth) && NOT ISBLANK(StartMonth), 0, 0),
        BLANK ()
    )

 

 

joyc0025_4-1685946924620.png

 

 

I've spent hours going in circles with ChatGPT but getting nowhere..

0 REPLIES 0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.