Calculating number of days in a month

Power BI
1

HI EDNA Team,

With regard to one of the tutorials in the Forecasting and Budgeting module, I have a simple question. Sam has calculated no. of days using the following formuala:

no of days = calculate( countrows(dates), ALLEXCEPT( ‘dates’, dates[Year]))

This is because for every date in the table, the number of days in a Year in which that particular date falls in ought to be ascertained, so that the budget amount which has been determined on a Yearly basis may be allotted for each day. That makes perfect sense.

My issue is that since my the budget I have is on a monthly basis, I am trying to calculate the number of days in a month using the above-mentioned formula, except replacing [Year] with [Month & Year]. However, for some reason, this is not working for me.

Here is the link to the video:

Budgeting Examples in Power BI using DAX | Enterprise DNA

Regards

Musa

2

@musa.fasih ,

Based on your description above, it seems that a simple COUNTROWS(Dates) measure should work here, taking advantage of the existing filter context, either in a matrix or table:

image

I hope this is helpful.

– Brian

3

HI Brian,

The only time it works for me is when I have the month/ year filter on. When I only select the present Financial Year from the filter (not the month & Year), the following appears:

image

Musa

4

Hello @musa.fasih,

Thank You for posting your query onto the Forum.

You can try the below provided measure alongwith the screenshot of the final results provided for the reference -

Days in Month = 
CALCULATE( 
    COUNTROWS( Dates ) , 
        ALL( Dates ) , 
            VALUES( Dates[Month & Year] ) )

Final Results
Final Results671×740 25 KB

I’m also attaching the working of the PBIX file for the reference purpose.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Days In A Month - Harsh.pbix (581.7 KB)

1 Like
5

Hi Harsh,

Worked perfectly fine. Thanks Alot!

6

Hello @musa.fasih,

You’re Welcome. :slightly_smiling_face:

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh