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.
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
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 |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |