When a table has only Years visible I want to get the last 91 days for last Year

[sue_mcc21]

Hi

I have the dax to get 91 days for previous year but when I try to put this in a visual with only the years showing I can not achieve this. I need to get the data from the filter which would be 9-10-2023 and get the last 91 days from the 9-10-2022. Any help much appreciated.

This is the code I have for retrieving the data which works fine when all the dates are in the visual

Qty Last 91 Days LY = CALCULATE(SUM('Data Details'[ Qty]),

SAMEPERIODLASTYEAR(

  DATESBETWEEN('Calendar SP1'[Date],  

  FIRSTDATE(DATEADD('Calendar SP1'[Date],-91,DAY)),

LASTDATE('Calendar SP1'[Date])

)))

Below is a snapshot of the outcome that I would like to achieve if possible.

Year	This Year	Last Year - Last 91 Days
2023	60000	105000
2022	136000
Total	196000	105000
Date	Value	91 Day Total
9/10/2022	1000
2/10/2022	2000
25/09/2022	3000
18/09/2022	4000
11/09/2022	5000
4/09/2022	6000
28/08/2022	7000
21/08/2022	8000
14/08/2022	9000
7/08/2022	10000
31/07/2022	11000
24/07/2022	12000
17/07/2022	13000
10/07/2022	14000	105000
17/07/2022	15000
24/07/2022	16000
Total	136000

Thankyou

[sue_mcc20]

Hi

Great thank you for this, however when I put it into a visual with Years only I get only the month from last year, I need the total for 91 days = 13 weeks

Thanks in advance

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[sue_mcc20]

Year	Qty	Qty 91- Days LY
2023	130000	105000
2022	136000
Date	Qty
9/10/2023	1000
2/10/2023	2500
25/09/2023	4000
18/09/2023	5500
11/09/2023	7000
4/09/2023	8500
28/08/2023	10000
21/08/2023	11500
14/08/2023	13000
7/08/2023	14500
31/07/2023	16000
24/07/2023	17500
17/07/2023	19000
9/10/2022	1000
2/10/2022	2000
25/09/2022	3000
18/09/2022	4000
11/09/2022	5000
4/09/2022	6000
28/08/2022	7000
21/08/2022	8000
14/08/2022	9000
7/08/2022	10000
31/07/2022	11000
24/07/2022	12000
17/07/2022	13000
10/07/2022	14000
17/07/2022	15000
24/07/2022	16000

[lbendlin]

Hmm, not getting the same result.

All of the 2022 transactions lie within the 91 day window.

[sue_mcc20]

Hi

Thank you that worked 

[sue_mcc20]

Hi

I'm sorry I tried to upload the sample PBIX file, I am using the code you provided.

Qty Last 91 Days LY =

var d = max('Calendar SP1'[Date])

return CALCULATE(SUM('Data Details'[Qty]),

SAMEPERIODLASTYEAR('Calendar SP1'[Date]),  

'Calendar SP1'[Date]>=EDATE(d,-12)

)

[lbendlin]

Qty Last 91 Days LY = 
var d = max('Calendar SP1'[Date])
return CALCULATE(SUM('Data Details'[ Qty]),
SAMEPERIODLASTYEAR('Calendar SP1'[Date]),  
'Calendar SP1'[Date]>=EDATE(d,-12)
)