Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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.
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.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
How the measure should be written so it calculate sum of units for year/week 201904 and 201905?
Thanks!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |