Calculate a measure based on number of days in a month

[Anonymous]

Hey community! I have a requirement to calculate a measure based on the total number of days in a month. Would love to get your help with it. 

For example - I need to divide the budget by number of days in a month. 

So - If the date slicer is set at January, then budget divided by 31 days, if Feb, then budget divided by 29 days, if march - budget divided by 30 days and so on. 

I tried to create a quick measure with filtered value but no luck yet 😞 

Monthly Budget for 30 days =

var a = CALCULATE([Monthly Budget]/30, 'Calendar'[MonthNo] IN { 7,9,11 })

var b = calculate([Monthly Budget]/31, 'Calendar'[MonthNo] IN { 6,8,10 })

return b 

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, you can try to create the following measure.

var a = [Monthly Budget]/30
var b = [Monthly Budget]/31
var c = [Monthly Budget]/28
var month_no = SELECTEDVALUE( 'Calendar'[MonthNo])
RETURN SWITCH(TRUE(),
month_no IN {1,3,5,7,8,10,12},b,
month_no IN {4,6,9,11},a,
c)

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

[amitchandak]

@Anonymous , Store target at last date of month and try formula like

Measure =
Var _day =TOTALMTD(Max('Date'[Date]),'Date'[Date])
return
CLOSINGBALANCEMONTH(divide(sum(Target[Target]),day(eomonth(_day,0)))*day(_day),'Date'[Date])

Refer : https://www.youtube.com/watch?v=yPQ9UV37LOU

[camargos88]

@Anonymous ,

Check the attached file, I've created this measure:

_QtdDays = 
VAR _minDate = CALCULATE(MIN('Calendar'[Date]), ALLSELECTED('Calendar'[Date]))
VAR _lastDate = EOMONTH(_minDate, 0)
RETURN DATEDIFF(_minDate, _lastDate, DAY) + 1

The EOMONTH function gives you the last day of the month, adding or note month to it.

https://docs.microsoft.com/en-us/dax/eomonth-function-dax

[Anonymous]

This is how it is done in Google data studio - case
when Month = 1 OR Month = 3 OR Month = 5 OR Month = 7 OR Month = 8 OR Month = 10 OR Month = 12 then 31 day budget
when Month = 4 OR Month = 6 OR Month = 9 OR Month = 11 Then Thirty Day Budget
else Feb Budget
END 

[Anonymous]

Hey there! I need to add one more calculation to the measure. 

For ex - If the month is Jan then I need to divide budget by no. of days(31) 

if Feb then I need to divide budget by no. of days(28) 

and so on. 

[amitchandak]

@Anonymous , not sure for whom the message is. You can try

Measure =
Var _day =TOTALMTD(Max('Date'[Date]),'Date'[Date])
return
CLOSINGBALANCEMONTH(divide(sum(Target[Target]),day(eomonth(_day,0))),'Date'[Date]) //divided by days of month

or

CLOSINGBALANCEMONTH(divide(sum(Target[Target]),day(eomonth(_day,0)))*day(_day),'Date'[Date])