Running 3 period total for Custom Periods

[Lightice83]

Trying to create a 3-period running total (current period & previous 2) where I am using custom periods (not dates). I can get it to work except I want the final value calculated to be the final period with values (in my example below I woud like the 2nd table to stop @ period '2022 - ON4')

I know I need to create an additional variable which calculates the final period & then put this as an IF statment at the beginning of the RETURN variable but just not sure of the formula

Thanks!!

=VAR Lastvisibleperiod = 
	MAX(Periods[Index])

VAR Firstvisibleperiod = 
	Lastvisibleperiod-2		
	

VAR Result = 
	CALCULATE(
		[IH - Sum of Sessions],
		Periods[Index]>=Firstvisibleperiod,
		Periods[Index]<=Lastvisibleperiod,
		ALL(Periods)
	)

RETURN
	Result

[Jihwan_Kim]

Hi,

I am not sure how your datamodel looks like, but please try the below measure whether it suits your requirement.

New measure: =
VAR Lastvisibleperiod =
    MAX ( Periods[Index] )
VAR Firstvisibleperiod = Lastvisibleperiod - 2
VAR Result =
    CALCULATE (
        [IH - Sum of Sessions],
        Periods[Index] >= Firstvisibleperiod,
        Periods[Index] <= Lastvisibleperiod,
        ALL ( Periods )
    )
RETURN
    IF (
        SELECTEDVALUE ( Periods[PeriodsName] ) = "2023-ON1"
            || SELECTEDVALUE ( Periods[PeriodsName] ) = "2023-ON2",
        [IH - Sum of Sessions],
        Result
    )

[Lightice83]

Thanks @Jihwan_Kim but I didn't want to hardcode in period names as this will change each time we update the datasource so basically I need the calculation to stop in the last period there is data for that period in the fact table. Is there a way to do this?

[Jihwan_Kim]

Hi,

Thank you for your feedback.

I think you can try to amend the logic something like below.

if the selected fiscal-year is the last fiscal year, then [IH - Sum of Sessions], otherwise, result.

Or, please share your sample pbix file's link here, and then I can try to look into it to come up with a more accurate solution.

Thank you.