DATESINPERIOD Function

[Caldowd98]

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 !

LTM Brand 1 = CALCULATE(SUM('Sheet1'[Quantity]), FILTER(Sheet1,Sheet1[Type]= "Brand 1" && DATESINPERIOD( Sheet1[Date].[Date], MAX( Sheet1[Date] ), -12, MONTH )))

[Anonymous · Accepted solution]

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

[Anonymous]

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

[Samarth_18]

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