Forum Discussion
CY vs LY
Hi Experts,
I am trying last many days to calculate measures but not able to do.
Actually, I want to calculate CY vs LY measures which has :
1) CY - All data for current year, but the problem is sales data has only data till 2021 year so if I don't select any year from year slicer then it should show 2021 data as current year data.
If I select 2021 from year slicer, then also it should show 2021 year data as CY data. If I select 2020 from year slicer then my CY will become 2020 year data.
2) LY - All months data of last year (Jan-20 to Dec-20). If I don't select any year from year slicer then it should show 2020 data as last year data.
If I select 2021 from year slicer, then also it should show 2020 year data as LY data. If I select 2020 from year slicer then my LY will become 2019 year data.
Below is the area chart which was asked me to create.
Attribute column has Dimention names. as you can see in filter , Order is selected so you can find this order in Attribute column as "No. Orders" name.
Value column has transaction counts.
My another problem is that this chart should be able to change values as per the slicer selection. means,
1- If I select Orders in slicer then it should show total sum of orders in area chart.
2- If I select Pack Sold in slicer then it should show total sum of Pack Sold in area chart.
Below is the PBIX file :
Thanks a lot . Kindly help me please
Hi Anonymous ,
To get the previous year calculation you need to use a Calendar table the calculation will be the same but linking to the calendar table:
Be aware that the calendar table is connected to the data table by order date but can be by created date.
Regarding the second part you need to use a switch measure:
CY Order = SWITCH(SELECTEDVALUE('Filter Table'[Value]), "Order",CALCULATE(sum(DataFiles[Value]),DataFiles[Attribute] = "No. Orders"), "Pack Sold",CALCULATE(sum(DataFiles[Value]),DataFiles[Attribute] = "Packs Sold"), "Revenue",CALCULATE(sum(DataFiles[Value]),DataFiles[Attribute] = "Revenue"), "Sell-Out",CALCULATE(sum(DataFiles[Value]),DataFiles[Attribute] = "Sell-Out"), "Sessions",CALCULATE(sum(DataFiles[Value]),DataFiles[Attribute] = "Sessions"), "Units", CALCULATE(sum(DataFiles[Value]),DataFiles[Attribute] = "Units Sold"))Check File attach.
8 Replies
- MFelix
Super User
Hi Anonymous ,
To get the previous year calculation you need to use a Calendar table the calculation will be the same but linking to the calendar table:
Be aware that the calendar table is connected to the data table by order date but can be by created date.
Regarding the second part you need to use a switch measure:
CY Order = SWITCH(SELECTEDVALUE('Filter Table'[Value]), "Order",CALCULATE(sum(DataFiles[Value]),DataFiles[Attribute] = "No. Orders"), "Pack Sold",CALCULATE(sum(DataFiles[Value]),DataFiles[Attribute] = "Packs Sold"), "Revenue",CALCULATE(sum(DataFiles[Value]),DataFiles[Attribute] = "Revenue"), "Sell-Out",CALCULATE(sum(DataFiles[Value]),DataFiles[Attribute] = "Sell-Out"), "Sessions",CALCULATE(sum(DataFiles[Value]),DataFiles[Attribute] = "Sessions"), "Units", CALCULATE(sum(DataFiles[Value]),DataFiles[Attribute] = "Units Sold"))Check File attach.
Thanks a lot MFelix This is what I was looking for.
It will be great if you can help me another point in the same file.
I want to apply top N over the CY Order measure. for example, if I select top 10, then measure should be able to give me top 10 CY Order value against Product ID. If I select top 20, then should give top 20 values .Thanks a lot in advance for your quick support. 🙂
- MFelix
Hi Anonymous ,
Do you want this to be based on a slicer or can you use the filter pane?
The easy way is to add the TOPN filter on the column and select the CY to be on your top N:
Then you can adjus for top 10 or 20.
MFelix , I want this with slicer. suppose I have a TOp N table with 10,20,30 and 50 value.
If I select 10 then show top 10 records and so on.
Thanks.