Count by month

[raymondchen]

Hi,

I have data like below: 

I want to create a visual on PowerBI to show the count of cases monthly, including opening balance (cases that opened in last month but not yet closed), added (cases that newly added in this month), close (cases that closed in this month), and closing balance (cases that still not closed by the end of this month). The outcome would be look like this

I just start learning DAX and have trouble of finding the correct functions to achieve.

I figured out of getting opening balance and added by using below: 

Opening Balance = CALCULATE(DISTINCTCOUNT('Table'[Case #]), PARALLELPERIOD('Table'[Date Reported].[Date], -1, MONTH))

Added = CALCULATE(DISTINCTCOUNT('Table'[Case #]), PARALLELPERIOD('Table'[Date Reported].[Date], 0, MONTH))

However, I cannot get the Closed and Closing Balance

Thank you for the help in advance.

[v-mengzhu-msft · Accepted solution]

Hi @raymondchen ,

Like this?

Please refer to my pbix file for better understanding.

Best regards,

Community Support Team Selina zhu

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

[Sandielly]

Hey, try to create a column in PQ with dates between Start and End date, then link your calendar table with this new created column, your model will be bigger but you will have a record for each day and you can count them afterwards.

[raymondchen]

Sorry I am not quite understand. What do you mean by Start and End date? And how to get the count by linking to calendar table?