Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a scenario in a Power BI Matrix visual, where I have a simple measure, called SalesAmount. It is a simple sum based on a single column of a table, called FactSales.
I have a Date dimension (Calendar) table in my model called Dates2.
I have developed a matrix report as below. This report uses a WeekNumber from the Dates2 table. I have also developed another measure called SalesAmount_SWLY to get the SalesAmount measure for the same week in the previous year. My Week Number cycle is Monday to Sunday (not the default Sunday to Saturday cycle).
(The first of the above 2 visuals is the actual report, the second one is just to show you, how the measure for the corresponding week of the previous year is stored.)
It is important to note that the measure, SalesAmount is NOT cumulative (non-YTD), i.e. for Week-10 of a year, we do NOT need to add Week-1 to Week-10 sales, but show only Week-10 sales. But the year totals are aggregated across all the weeks for a given year.
I then develop a measure to generate the percentage change, in parallel weeks. For example, Week-1 of 2020 is compared with Week-1 of 2019, to develop the % change.
Formula for % change in Week-1 of 2020 = {(Value in Week-1 of 2020) - (Value in Week-1 of 2019)}/ (Value in Week-1 of 2019)
Formula for % change in 2020 = {(Total of all the weeks of 2020) - (Total of all the weeks of 2019)}/ (Total of all the weeks of 2019)
This works fine, but for one issue. In the year 2021, we have data only for 2 weeks. (Assume that as of today, we have completed only 2 weeks in 2021, even though we are in September)
As far as the percentage change is concerned, for the aggregated total of 2021 (the last column of the matrix on the right side), we need to compare the aggregated total of the first 2 weeks of 2021, with the corresponding total of the first two weeks of 2020.
In the visual above, I do not want to see -95.66%, but -18.75%.
Logic: we have completed only two weeks in 2021
Aggregated value in 2021 = 13 (i.e. 6 + 7)
Aggregated value in 2020 = 16 (i.e. 1 + 15, for the first two weeks alone in 2020)
(Currently the 2020 value is taken as 300, which is incorrect)
Percentage change = (13-16)/16 = -18.75 % (needed in the report)
Percentage change = (13-300)/300 = -95.66 % (currently shown in the report, incorrect)
I posted a similar question a few months back, for percentage change across months across different years:
Can someone help me solve this percentage change issue across Week Numbers, using a new measure in DAX ?
Logic:
I am using the 7-day cycle from Monday to Sunday, to compute week numbers in the Dates2 table. The column name is WeekNumber in the Dates2 table. I compute the measure for the same week number last year, using the below formula:
SalesAmount_SWLY = CALCULATE(
[SalesAmount],
FILTER(
ALL(Dates2),
Dates2[Year] = SELECTEDVALUE(Dates2[Year]) - 1
&&
Dates2[WeekNumber] = SELECTEDVALUE(Dates2[WeekNumber])
)
)
I am fine in the way I compute percentage change between individual weeks of different years, say Week-5 of 2020 and Week-5 of 2019. Similary the corresponding week numbers of any two consecutive years.
I have a challenge in computing the yearly (grand) percentage change between the most recent year (2021), and the previous year (2020) (this is because, in 2021, we have not completed all the weeks)
I have just 'n' weeks in 2021. The grand total for 2021 across all the 'n' weeks is fine; n < 53 (mostly, until the full year is completed).
I go to the previous year 2020. Find the date of the 7th day of the nth week of 2020. Let me call this date X. This is considered the end date for the previous year (2020).
The grand total for 2020, is computed from Jan 1 to date X. This value is used to compute the yearly percentage change from 2020 to 2021.
For the yearly (grand) percentage change across consecutive years, I need to take the grand total for both the years, only when both years have 53 weeks of data. For the most recent year (say 2021), I will most often not have all the weeks. In this case, between 2021 and 2020 (only), I compare the data only for the first 'n' weeks. For all other cases, things are normal.
Solved! Go to Solution.
Hi @snph1777
I think you need to update your SalesAmount_SWLY Measure.
SalesAmount_SWLY =
VAR _LastYear =
YEAR ( MAXX ( ALL ( 'Table' ), 'Table'[Date] ) )
VAR _LastNotblankDateinSales =
MAXX (
FILTER (
ALL ( 'Table' ),
YEAR ( 'Table'[Date] ) = MAX ( Dates2[Year] )
&& 'Table'[Sales] <> BLANK ()
),
'Table'[Date]
)
VAR _MaxWeekNum =
IF (
MAX ( Dates2[Year] ) = _LastYear,
WEEKNUM ( _LastNotblankDateinSales, 2 ),
53
)
VAR _LastYearBody =
SUMX (
FILTER (
ALL ( Dates2 ),
Dates2[Year]
= SELECTEDVALUE ( Dates2[Year] ) - 1
&& Dates2[WeekNum] = SELECTEDVALUE ( Dates2[WeekNum] )
),
[SalesAmount]
)
VAR _LastYearTotal =
SUMX (
FILTER (
ALL ( Dates2 ),
Dates2[Year]
= SELECTEDVALUE ( Dates2[Year] ) - 1
&& Dates2[WeekNo] <= _MaxWeekNum
),
[SalesAmount]
)
RETURN
IF ( HASONEVALUE ( Dates2[WeekNum] ), _LastYearBody, _LastYearTotal )
In my sample Total in 2016 is 15, and 2017 is end in "Week 50". Total percentage in 2017 should be (22-15)/15 = 46.67%
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Any help ? Can someone suggest something ? If you need clarity in the question, please feel free to ask me.
Hi @snph1777
I think you met difficulties in calculating the precentage in 2021 and 2020 in Total Part in your matrix.
Here I build a sample to have a test. My sample has the same value in Week1, Week2 ,Week3 and Week53 as you shown in your screenshot. If my measure is correct, it will show 18.75% in 2021 Total. (I have week53 in 2020)
My Dates2 is as below.
Dates2 =
ADDCOLUMNS (
CALENDAR ( DATE ( 2018, 01, 01 ), DATE ( 2021, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"YearMonth",
YEAR ( [Date] ) * 100
+ MONTH ( [Date] ),
"WeekNo", WEEKNUM ( [Date], 2 ),
"WeekNum",
"Week" & "-"
& WEEKNUM ( [Date], 2 )
)
Relationship:
Measure is as below.
SalesAmount = SUM('Table'[Sales])
SalesAmount_SWLY =
VAR _LastNotblankDateinSales =
MAXX (
FILTER (
ALL ( 'Table' ),
YEAR ( 'Table'[Date] ) = MAX ( Dates2[Year] )
&& 'Table'[Sales] <> BLANK ()
),
'Table'[Date]
)
VAR _MaxWeekNum =
WEEKNUM ( _LastNotblankDateinSales, 2 )
VAR _LastYearBody =
SUMX (
FILTER (
ALL ( Dates2 ),
Dates2[Year]
= SELECTEDVALUE ( Dates2[Year] ) - 1
&& Dates2[WeekNum] = SELECTEDVALUE ( Dates2[WeekNum] )
),
[SalesAmount]
)
VAR _LastYearTotal =
SUMX (
FILTER (
ALL ( Dates2 ),
Dates2[Year]
= SELECTEDVALUE ( Dates2[Year] ) - 1
&& Dates2[WeekNo] <= _MaxWeekNum
),
[SalesAmount]
)
RETURN
IF ( HASONEVALUE ( Dates2[WeekNum] ), _LastYearBody, _LastYearTotal )
Percentage = DIVIDE([SalesAmount]-[SalesAmount_SWLY],[SalesAmount_SWLY])
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-rzhou-msft thanks very much; i will try your solution and get back; appreciate your help
Will this work, when I do not have 53 weeks in any earlier year (other than 2021) ?
Say 2016, I have all 53 weeks, but in 2017 I have only the first 50 weeks (no data in Week-51 or Week-52 or Week-53), because no data was recorded for the last 3 weeks of 2017.
When I do the % comparison between 2017 and 2016, in a case like this, I need the grand total of the matrix for 2017 to use all the weeks of both the years, and NOT stop with only 50 weeks of 2016.
In essence, I need this logic of cutting the weeks short for the previous year, ONLY for the current year, i.e. 2021, and when we are yet to complete all the 53 weeks of the current year.
Hi @snph1777
I think you need to update your SalesAmount_SWLY Measure.
SalesAmount_SWLY =
VAR _LastYear =
YEAR ( MAXX ( ALL ( 'Table' ), 'Table'[Date] ) )
VAR _LastNotblankDateinSales =
MAXX (
FILTER (
ALL ( 'Table' ),
YEAR ( 'Table'[Date] ) = MAX ( Dates2[Year] )
&& 'Table'[Sales] <> BLANK ()
),
'Table'[Date]
)
VAR _MaxWeekNum =
IF (
MAX ( Dates2[Year] ) = _LastYear,
WEEKNUM ( _LastNotblankDateinSales, 2 ),
53
)
VAR _LastYearBody =
SUMX (
FILTER (
ALL ( Dates2 ),
Dates2[Year]
= SELECTEDVALUE ( Dates2[Year] ) - 1
&& Dates2[WeekNum] = SELECTEDVALUE ( Dates2[WeekNum] )
),
[SalesAmount]
)
VAR _LastYearTotal =
SUMX (
FILTER (
ALL ( Dates2 ),
Dates2[Year]
= SELECTEDVALUE ( Dates2[Year] ) - 1
&& Dates2[WeekNo] <= _MaxWeekNum
),
[SalesAmount]
)
RETURN
IF ( HASONEVALUE ( Dates2[WeekNum] ), _LastYearBody, _LastYearTotal )
In my sample Total in 2016 is 15, and 2017 is end in "Week 50". Total percentage in 2017 should be (22-15)/15 = 46.67%
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sure I will; I will certainly finalize this, in a day or two, this week for sure. I am held up in some other work, but will focus on this one from Tuesday or Wednesday.
Hi @snph1777
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file.
Best Regards,
Rico Zhou
SalesAmount_SWLY =
VAR LastYear = YEAR(
MAXX(
ALL(FactSales),
FactSales[Date]
)
)
VAR LastDateOfRecordedSales = MAXX(
FILTER(
ALL(FactSales),
YEAR(FactSales[Date]) = MAX(Dates2[Year])
&&
ISBLANK(FactSales[Sales]) = FALSE
),
FactSales[Date]
)
VAR MaxWeekNumber = IF(
MAX(Dates2[Year]) = LastYear,
WEEKNUM(LastDateOfRecordedSales, 2),
53
)
VAR SWLY = CALCULATE(
[SalesAmount],
FILTER(
ALL(Dates2),
Dates2[Year] = SELECTEDVALUE(Dates2[Year]) - 1
&&
Dates2[WeekNumber] = SELECTEDVALUE(Dates2[WeekNumber])
)
)
VAR SWLYtotal = CALCULATE(
[SalesAmount],
FILTER(
ALL(Dates2),
Dates2[Year] = SELECTEDVALUE(Dates2[Year]) - 1
&&
Dates2[WeekNumber] <= MaxWeekNumber
)
)
RETURN
IF(
(HASONEVALUE(Dates2[Year]) = TRUE && HASONEVALUE(Dates2[WeekNumber]) = TRUE),
SWLY,
IF(
(HASONEVALUE(Dates2[Year]) = TRUE && HASONEVALUE(Dates2[WeekNumber]) = FALSE),
SWLYtotal,
BLANK()
)
)
I have slightly cleaned up the code given by @RicoZhou ; thanks very much @RicoZhou ; this solution is acceptable.
I will get back in the the next 24-48 hours. Sorry for the delay. (I have been held up in some other task, but this DAX issue is still a top priority).
User | Count |
---|---|
90 | |
85 | |
66 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |