Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 😞
Solved! Go to 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.
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.
@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
@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
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
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.
@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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |