How to change a temporal dimension based on a filter

[aresb]

Hi everyone,

i've the dimension code_year_month which is a string contains 202001, 202002 202003...etc

I setup this sheet and i want that the table display the previous year showing 201901 201902...etc

How can i achieve that?

[vinieme12 · Accepted solution]

Derive attributes from your current field such as year,month, date in your data load script

As below 

,DATE(Date#(code_year_month&'01','YYYYMMDD')) AS MonthStartDate

,Year(Date#(code_year_month,'YYYYMM')) AS YEARFIELD 

,MONTH(Date#(code_year_month,'YYYYMM')) AS MONTHFIELD 

Then in charts you can use either of below expressions

=Sum({<YEARFIELD={"$(=MAX(YEARFIELD,2))"}>}Something)

OR

=Sum({<MonthStartDate={">=$(=Date(Yearstart(MAX(MonthStartDate),-1)))<=$(=Date(Yearend(MAX(MonthStartDate),-1)))"}>}Something)