Create DAX to count number of cases per month in PowerBI

[anastasia23]

Hi everyone,

In my dataset I have the case number, repair arrived date and repair finished date.

I want to create 2 different measures in order to count how many cases arrived per month and how many cases finished per month.c

I used the following measure to calculate the number of cases that arrived per month, but I see the same number of cases for all the months. Could anyone help me?

Monthly cases arrived = VAR StartDate = DATE(2022,11,1) VAR EndDate = DATE(2023,5,8) return CALCULATE(COUNT('All cases'[Case Number]), 'All cases'[Repair Arrived Date]>= StartDate, 'All cases'[Repair Arrived Date]<= EndDate, DATESBETWEEN('Date'[Date], StartDate, EndDate))

I try to count the number of cases arrived and finished per month

Thank you.enter image description here

[v-jianboli-msft · Accepted solution]

Hi @anastasia23 ,

Please try:

First create a Date table:

Then apply these measures:

Monthly cases arrived = 
CALCULATE(DISTINCTCOUNT('All cases'[Case Number]),FILTER('All cases',[Repair Arrived Date]>=MIN('Calendar'[Date])&&[Repair Arrived Date]<=MAX('Calendar'[Date])))

Monthly cases finished = 
CALCULATE(DISTINCTCOUNT('All cases'[Case Number]),FILTER('All cases',[Repair Finished Date]>=MIN('Calendar'[Date])&&[Repair Finished Date]<=MAX('Calendar'[Date])))

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523