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

joyc0025 — Mon, 05 Jun 2023 06:39:05 GMT

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

Table: Purchasing Location

Date Table:

Unrelated slicer date table: 'Date_Workflow Approvals'

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 () )

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

topic Measure to count a category by month (+ criteria) to visualize in a line chart - not break line in DAX Commands and Tips

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