Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
kw
Frequent Visitor

Dax Measure for pro-rated days

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

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

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))

 

q3.PNG

 

You can download attached .pbix file to have a look.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-qiuyu-msft
Community Support
Community Support

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))

 

q3.PNG

 

You can download attached .pbix file to have a look.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.