Dax Measure for pro-rated days

[kw]

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

[v-qiuyu-msft · Accepted 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

[kw]

Hi @v-qiuyu-msft

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