Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Multiple selection slicer values on Measure

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

YearWeekMonthDate
202052January 27, 2020
202052January 28, 2020
202052January 29, 2020
202052January 30, 2020
202052January 31, 2020
202052February 1, 2020
202052February 2, 2020
201952January 28, 2019
201952January 29, 2019
201952January 30, 2019
201952January 31, 2019
201952February 1, 2019
201952February 2, 2019
201952February 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!

 

 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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))

 

1.PNG2.PNG

 

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.

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

4 REPLIES 4
v-jayw-msft
Community Support
Community Support

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))

 

1.PNG2.PNG

 

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.

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
Not applicable

Thanks @v-jayw-msft 

 

Your approach solved my problem 😉

 

MFelix
Super User
Super User

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)
      )          

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.