Forum Discussion
DATESINPERIOD Function
Hi Community
Im using the Datesinperiod funtion will the below DAX formula, however it doesnt seem to work with the filter in.
Any ideas ?
Many Thanks !
Hi Caldowd98 ,
Here are the steps you can follow：
1. Create measure.
Measure = CALCULATE(SUM('Sheet1'[Quantity]),FILTER(ALLSELECTED('Sheet1'),'Sheet1'[Type]="Brand 1"&&'Sheet1'[Date]>=DATE(YEAR(MAX('Sheet1'[Date]))-1,MONTH(MAX('Sheet1'[Date])),DAY(MAX('Sheet1'[Date])))&&'Sheet1'[Date]<=MAX('Sheet1'[Date])))2. Result:
Will vary with the filter: say, filter out October 2021, and the result will vary
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
Hi Caldowd98 ,
Here are the steps you can follow：
1. Create measure.
Measure = CALCULATE(SUM('Sheet1'[Quantity]),FILTER(ALLSELECTED('Sheet1'),'Sheet1'[Type]="Brand 1"&&'Sheet1'[Date]>=DATE(YEAR(MAX('Sheet1'[Date]))-1,MONTH(MAX('Sheet1'[Date])),DAY(MAX('Sheet1'[Date])))&&'Sheet1'[Date]<=MAX('Sheet1'[Date])))2. Result:
Will vary with the filter: say, filter out October 2021, and the result will vary
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Such a huge help.
Was able to fix my orginal formula that didn't work:
Closer Rate YTD move back 3 months =
CALCULATE(
1-DIVIDE(
SUM('Monthly Data'[Still to be invoiced (val.)]),
SUM('Monthly Data'[Net Order Value])),
DATESINPERIOD(
'Calendar'[Date],
ENDOFMONTH(DATEADD('Calendar'[Date],-3,MONTH)),
-1,
YEAR)
)To a formula that did work:
Closer Rate YTD Roll Back 3 months =
CALCULATE(
1-DIVIDE(
SUM('Monthly Data'[Still to be invoiced (val.)]),
SUM('Monthly Data'[Net Order Value])),
FILTER(ALLSELECTED('Monthly Data'),
'Monthly Data'[Document Date]>=DATE(YEAR(MAX('Monthly Data'[Document Date])),
MONTH(MAX('Monthly Data'[Document Date]))-15,
DAY(MAX('Monthly Data'[Document Date])))&&
'Monthly Data'[Document Date]<=DATE(YEAR(MAX('Monthly Data'[Document Date])),
MONTH(MAX('Monthly Data'[Document Date]))-3,
DAY(MAX('Monthly Data'[Document Date])
)
)))Thank you so much!!!!!
Hi Caldowd98 ,
Kindly share some sample data in text format with desired output, it will help us to answer more specifically. 🙂
NOTE:- Time intelligence functions work well when you have date table in your model. Try to add calender table into the model then hopefully you will get desired output.
Thanks,
Samarth
