Question wrt Lag working dynamically with period type

[Madura_Puri]

Hi,

I have a Reporting Period control that changes date aggregation based on Week, Month, Quarter, Year. I’m trying to calculate the PoP percent difference based on the reporting period selected. Tried a lag function but it’s not quite giving me correct values for the previous spend. (See image). Can this be achieved? There are inbuilt periodOverPeriodDifference and PeriodOverPeriod percent difference but can they used with the date aggregation dynamically? Thanks a lot!

The formula for the field Lag_Test_Previous_Val is

lag(
sum({delivered_spend}),
[{Reporting_Period} ASC],1

)

[Max]

I would look at this article!

It should have everything you are looking for!

Creating period comparisons with QuickSight's periodOver and periodTo functions

This article uses the SaaS-Sales Dataset that you can download as part of the QuickSight Author Workshop. We will update the data to the current date so your calculations are realistic. If you do not have a QuickSight account please follow the instructions in the Initial Setup. Instructions to create the dataset can be found in Exercise 1 of the Build your first dashboard. Create an analysis for the dataset. The following steps show you how to create visuals to show data from the current year,…