DAX Code for Previous Year Rolling Averages

[HollyPludeTFA]

I have read numerous posts on issues surround Rolling Averages, but I have not found one that answers my specific problem.

I have a dashboard for the 2022 Fiscal Year. I need to write a formula that creates a 4 month (and then a separate formula for a 12 month) rolling average, but for the SPLY in 2022. I have used PREVIOUSYEAR, DATESBETWEEN, SAMEPERIODLASTYEAR all to no avail. Either these are the wrong expressions or I am really not understanding how to write this code.

This is my current code. It works exactly as it should for the current year, but even when utilizing date filters on the page, correct results are not returned.

Starts units R4M =

VAR NumOfMonths = 4

VAR LastCurrentDate =

    MAX ( Dates[Date] )

VAR Period =

    DATESINPERIOD ( Dates[Date], LastCurrentDate, - NumOfMonths, MONTH )

VAR Result =

    CALCULATE (

        AVERAGEX (

            VALUES ( Dates[Month] ),

            'Key Measures'[Frame]

        ),

        Period

    )

VAR FirstDateInPeriod = MINX ( Period, Dates[Date])

VAR LastDateWithSales = MAX ( SCHEDULE_SACTIVITY[SCH_START_DATE])

RETURN

    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

This is the code for the 'Key Measures' [Frame] portion of the code:

Frame =

CALCULATE(COUNT(SCHEDULE_SACTIVITY[SACTIVITY_ID]),

USERELATIONSHIP(Dates[Date],SCHEDULE_SACTIVITY[ACTUAL_START_DATE]),

SCHEDULE_SACTIVITY[SACTIVITY_ID] = 82

)

Thank you in advance for any assistance you might be able to lend me.

[HollyPludeTFA]

SS Sales Summary SANDBOX dummy.pbix

I moved the PBIX from Google Drive to OneDrive. I tested the link and it does work now.

Holly

[Ashish_Mathur]

Hi,

Share a small easy to understand dataset and show the expected result in a simple table format on that dummy dataset.

[Anonymous]

Hi @HollyPludeTFA ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[HollyPludeTFA]

Xiaoxin,

Thank you - I am scrubbing a subset of data and will upload it to a pbix and share.

Holly

[HollyPludeTFA]

https://drive.google.com/file/d/1fS5OnXZGRuRkqIR3X46qT_wejblmrdLY/view?usp=drive_link 

This is a link to my pbix.

Holly

[Ashish_Mathur]

Access Denied message.  Share data in a format that can be pasted in an MS Excel file and show the expected result.

[HollyPludeTFA]

Is this what you are looking for?

Start Date	Frame Count	Starts Avg R4M	Starts Avg R12M
01/01/22	1	0.00	0.00
02/01/22	4	0.00	0.00
03/01/22	1	0.00	0.00
04/01/22	1	1.75	0.00
05/01/22	6	3.00	0.00
06/01/22	1	2.25	0.00
07/01/22	7	3.75	0.00
08/01/22	4	4.50	0.00
09/01/22	1	3.25	0.00
10/01/22	1	3.25	0.00
11/01/22	2	2.00	2.64
12/01/22	3	1.75	2.82
01/01/23	6	3.00	3.00
02/01/23	2	3.25	3.09
03/01/23	3	3.50	3.27
04/01/23	2	3.25	2.91
05/01/23	1	2.00	2.91
06/01/23	1	1.75	2.36
07/01/23	3	1.75	2.27

[Anonymous]

Hi @HollyPludeTFA ,

Did Ashish_Mathur 's suggestions help with your scenario? If that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.

If that also doesn't help, please share more detailed information to help us clarify your scenario and test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[HollyPludeTFA]

Unfortunately the solution did not work. At this time, my client decided to simply remove the graphic from the dashboard.

Thank you! I appreciate the attention given to this issue.

Holly

[Ashish_Mathur]

Hi,

Please find attached the PBI file.  DIY for 12 months based on my measure for 4 monts.

Hope this helps.