Hi @sherif.elshaer -
I’ve confirmed the lag function will allow you to nest sumOver within it without applying aggregation . Thank you for raising this concern here. I’ve reproduced the error and will be investigating.
To use a LAC-W within a table calculation you can use
lag(max(sumover({sales},[],PRE_FILTER)),[financialyear ASC],1,[{Level 2},{Level 3},{Level 4},{Level 5}])
For your use case, the lag function won’t help here since it’s a table calculation. What this means is that the data must be in your visual dataset, or to use an analogy the lag function can only access what’s “on the table”. Once you filter your visual to a specific year like 2016 then lag can’t access the 2015 rows. Check out this order of evaluation doc for more detail.
To accomplish what you want you’ll need to use parameters. Check out this awesome guide for some approaches Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc)
Example:
Step 1 - Create a Year Parameter and add a control for it.
Step 2 - Create a current year sales calculated field
c_sales_current_year
sumif(Sales,{c_extract_year}=${pYear})
Step 3 - Create a previous year sales calculated field
c_sales_previous_year
sumif(Sales,{c_extract_year}=${pYear}-1)
Result:
