# Calculating number of days in a month

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

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:

– Brian

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:

Musa

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] ) )
``````

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.

Thanks and Warm Regards,
Harsh

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

1 Like

Hi Harsh,

Worked perfectly fine. Thanks Alot!

Hello @musa.fasih,

You’re Welcome.

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

Thanks and Warm Regards,
Harsh