topic Re: Count by month in DAX Commands and Tips

Count by month

raymondchen — Wed, 09 Nov 2022 18:43:03 GMT

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.

Re: Count by month

Sandielly — Wed, 09 Nov 2022 19:45:25 GMT

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.

Re: Count by month

raymondchen — Thu, 10 Nov 2022 15:45:17 GMT

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?

Re: Count by month

Anonymous — Tue, 22 Nov 2022 06:23:08 GMT

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