Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
I need help on a measure that returns a value
measure = [Days in Month] / [Days of Sales]
The number of days in a month measure is something like this
Days in Month = Day(LASTDATE('DateKey'[Date]))
The number of sales day for the month measure
Days of Sales = Day(Today())-1
I may be wrong on the above Days of Sales too, since if today is 1st it would return 0.
However the real issue is if it is not current year and month, it should take the number to days in the selected year & month divided by itself (ie = 1). The page is filtered by a Year and Month slicer.
Eg if 2015 Jun
meausure = 30/30
if today is 7 Jun 2017
measure = 30/6
Thanks
kw
Solved! Go to Solution.
Hi @kw,
You can create a measure like below:
Measure = var DaysInMonth=CALCULATE(DAY(LASTDATE('Date'[Date])),FILTER(ALL('Date'),MONTH('Date'[Date])=MAX('Table1'[Month]) && YEAR('Date'[Date])=MAX('Table1'[Year])))
var Sa=DAY(TODAY())-1
return
IF(MAX('Table1'[Year])=YEAR(TODAY()) && MAX('Table1'[Month])=MONTH(TODAY()),DIVIDE(DaysInMonth,Sa),DIVIDE(DaysInMonth,DaysInMonth))
You can download attached .pbix file to have a look.
Best Regards,
Qiuyun Yu
Hi @kw,
You can create a measure like below:
Measure = var DaysInMonth=CALCULATE(DAY(LASTDATE('Date'[Date])),FILTER(ALL('Date'),MONTH('Date'[Date])=MAX('Table1'[Month]) && YEAR('Date'[Date])=MAX('Table1'[Year])))
var Sa=DAY(TODAY())-1
return
IF(MAX('Table1'[Year])=YEAR(TODAY()) && MAX('Table1'[Month])=MONTH(TODAY()),DIVIDE(DaysInMonth,Sa),DIVIDE(DaysInMonth,DaysInMonth))
You can download attached .pbix file to have a look.
Best Regards,
Qiuyun Yu
Thank for your reply. I got a blank when evaluating your first var.
But simply using DAY(LASTDATE('Date'[Date]) seems to work for me.
Regards
kw