Want to show current year sale and last year sale in same row with dimension month year

[Sunilkumar3]

Hi Team,

I want show CY SALE and  PY SALE in same row if I select any month year from the dimension the KPI show should be in same Row. below are the

E.g.

Month_year	CY Sales	LY sale
Jan-22		15
Feb-22		25
Mar-22		28
Apr-22		41
Jan-23	10
Feb-23	20
Mar-23	30
Apr-23	40

 If I select Jan-2023.Result should be below

Month_year	CY Sales	LY sale
Jan-23	10	15

So how to write the expression for that

[Steven35 · Accepted solution]

Hello,

I am thinking of two different solutions :

1 - The simplest but not the best : You create a Month dimension.
In the script:
Month(Month_year) as Month

 You put Month in the straight table and then in the set Analysis measures:
For CY : Sum({<Year={"=$(=Year(today()))"}>} Sales)
For PY : Sum({<Year={"=$(=Year(today())-1)"}>} Sales)

2 - In the script in your calendar table you create several types of calendar, one for CY and one for PY.
It should look something like this:

Month_Year_Data	Calendar_Type	Month_Year
Jan 2023	CY	Jan 2023
Feb 2023	CY	Feb 2023
Jan 2022	PY	Jan 2023
Feb 2022	PY	Feb 2023

You put Month_Year in the straight table and then in the set Analysis measures:
For CY : Sum({<Calendar_Type={'CY'}>} Sales)
For PY : Sum({<Calendar_Type={'PY'}>} Sales)

I hope that's understandable enough.

Best regards,

Steven