Multiple selection slicer values on Measure

[Anonymous]

Hi Folks!

Hope you can throw a light on how to achieve this..

I'm working on a report to compare sold units, comparing CY (current year) versus LY (last year).

I'm using retail calendar definition so I have 2 slicers,

Slicer1:  to select Retail Month (that includes several retail weeks)

Slicer2: To select Retail Week

Since I'm using a retail calendar, I can't use the function SAMEPERIODLASTYEAR as month 2 of 2020 doesn't have the same exact dates of 2019

Year	Week	Month	Date
2020	5	2	January 27, 2020
2020	5	2	January 28, 2020
2020	5	2	January 29, 2020
2020	5	2	January 30, 2020
2020	5	2	January 31, 2020
2020	5	2	February 1, 2020
2020	5	2	February 2, 2020
2019	5	2	January 28, 2019
2019	5	2	January 29, 2019
2019	5	2	January 30, 2019
2019	5	2	January 31, 2019
2019	5	2	February 1, 2019
2019	5	2	February 2, 2019
2019	5	2	February 3, 2019

Depending on the values selected on each slicer , the measure evaluates the values for CY and LY.

For CY is pretty straight forward, but for Last Year I can't make it work If I select more than 1 month or week on the slicer.

Here is my formula:

 for CY --> 

Units CY = SUM(Sales_Retail[units])+SUM(SELLOUT_SALES[units])

for LY:
Var CurrentYear =SELECTEDVALUE(Retail_Calendar[year])
Var CurrentMonth =IF(HASONEVALUE(Retail_Calendar[Month]),VALUES(Retail_Calendar[Month]),BLANK())
Return
IF(
    ISBLANK(CurrentMonth),
        CALCULATE( [Units CY],FILTER(ALL(Retail_Calendar),Retail_Calendar[year]=CurrentYear-1))
         , CALCULATE([Units CY],FILTER(ALL(Retail_Calendar),Retail_Calendar[Month] = CurrentMonth &&            Retail_Calendar[year]=CurrentYear-1))
      )                       

For 1 month selected, works fine

When select more than one, it assumes no selection was made and shows total of 12 months.

I tried using several approaches but none of them worked...

Would appreciate any idea on how to achieve this

Thanks!

[Anonymous · Accepted solution]

Hi @Anonymous ,

Make sure the carlendar table do not have active relationship with sales table.

Please check the measures.

C_Y = CALCULATE(SUM(Sales_Retail[units]),FILTER(Sales_Retail,WEEKNUM(Sales_Retail[Date]) in VALUES('calendar'[Week])&&MONTH(Sales_Retail[Date]) in VALUES('calendar'[Month])&&YEAR(Sales_Retail[Date]) = SELECTEDVALUE('calendar'[Year])))

L_Y = CALCULATE(SUM(Sales_Retail[units]),FILTER(Sales_Retail,WEEKNUM(Sales_Retail[Date]) in VALUES('calendar'[Week])&&MONTH(Sales_Retail[Date]) in VALUES('calendar'[Month])&&YEAR(Sales_Retail[Date]) = SELECTEDVALUE('calendar'[Year])-1))

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @Anonymous 

Your approach solved my problem 😉

[MFelix]

Hi @Anonymous ,

When you refer to calculating the previous year you want to have the values based on date from January 27th to 2nd? or do you want it by week of the year?

If this is the last option (assuming it is because of the two slicers try to make an additional column on the calendar with year/week:

YEAR/WEEK= FORMAT(Retail_Calendar[Date];"yyyy")&FORMAT(WEEKNUM(Retail_Calendar[Date]);"00")

Now try to do the following code:

for LY:
Var CurrentYear_week =SELECTEDVALUE(Retail_Calendar[Year/week])
Return
IF(
    ISBLANK(CurrentYear/week),
        CALCULATE( [Units CY],FILTER(ALL(Retail_Calendar),Retail_Calendar[year]=CurrentYear-1))
         , CALCULATE([Units CY],FILTER(ALL(Retail_Calendar),Retail_Calendar[Year/week] = CurrentYear_week - 100)
      )          

[Anonymous]

Hi @MFelix 

Thanks for youy reply.

I'm not sure I explained myself correctly, the problem I have is that the formula does not evaluate when I select more than 1 week on the slicer.
Lets say I implemented your proposed column on the Retail_Calendar table and on the slicer I select weeks 4 & 5
Values on Var 

currentYear_week should be 202004 and 202005.

How the measure should be written so it calculate sum of units for year/week 201904 and 201905?

Thanks!