Forum Discussion
DATESINPERIOD Function
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
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!!!!!