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
snph1777
Helper V
Helper V

Microsoft Power BI and DAX - compute percentage change across PY week numbers - non YTD measures

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

 

src41.GIF

 

src42.GIF

 

(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.

 

src43.GIF

 

 

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:

 

https://community.powerbi.com/t5/Desktop/Microsoft-Power-BI-DAX-Time-Intelligence-measure-reflect-pr...

 

Can someone help me solve this percentage change issue across Week Numbers, using a new measure in DAX ?

 

 

Logic:

  1. 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])

                                                                         )

                                                            )

  2. 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.

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

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

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

  6. 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.

1 ACCEPTED 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%

1.png

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.

View solution in original post

10 REPLIES 10
snph1777
Helper V
Helper V

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)

1.png

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:

1.png

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.

1.png

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

@v-rzhou-msft 

 

 

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%

1.png

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

@RicoZhou   

 

Instead of using SUMX in the variables, can we use CALCULATE ?  Will that change the result ? I am more comfortable in using CALCULATE, instead of SUMX and a measure inside it ? 

 

 

I have a cleaned-up DAX measure below, using the logic provided by @RicoZhou 

 

 

 

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.

 

@v-rzhou-msft   

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

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.