Forum Discussion

echow's avatar
echow
Helper II
5 years ago
Solved

Help with Average Weekly DAX formula by comparing same period last year

I need to create a weekly sales by member comparing between 2020 and 2019.

The part I am struggling is how to obtain the LY Count column for both A and B category in DAX

 

The desire outcome table of my data is something like this

 

My final desire outcome is for the bar chart to look like something below.

So if I were to choose my filter 202042 and 202043 it will give me the outcome below

Calculation for Category A would be 

Blue Bar: (112.58+65.79)/(8+7) = $11.89

Orange Bar: (172.69+68.23)/(17+6) = $10.47

And so forth for Category B

 

So if I were to dynamically select 202043 only then it should be giving me

Blue Bar: 65.79/7 = $9.4

Orange Bar: 68.23/6 = $11.37

 

Here is my set of data

CALENDAR_DATEMBR_NO Sales ItemCategory
14/10/20191 $        10.00A1A
14/10/20193 $          2.00B3B
14/10/20193 $          3.00B3B
14/10/20194 $          7.65A4A
14/10/20195 $        19.66A5A
15/10/20196 $          0.57A6A
15/10/20197 $        10.34A7A
15/10/20199 $          1.91A9A
15/10/201913 $          7.18A13A
15/10/201914 $        10.46B14B
15/10/201914 $          0.13B14B
16/10/201918 $          5.67A18A
16/10/201918 $          7.45A18A
16/10/201921 $          5.37A21A
17/10/201922 $        15.03A22A
17/10/201923 $          8.45B23B
17/10/201924 $        17.73B24B
17/10/201933 $          8.16B33B
18/10/201936 $          2.71B36B
18/10/201937 $        13.85B37B
18/10/201940 $          1.66A40A
18/10/201940 $        13.41A40A
18/10/201940 $          5.60B40B
18/10/201941 $        17.83B41B
18/10/201942 $        19.64A42A
19/10/201943 $        10.17A43A
19/10/201944 $        14.85B44B
19/10/201944 $        13.47A44A
20/10/201951 $          0.28A51A
20/10/201952 $          3.53B52B
20/10/201955 $        18.06A55A
20/10/201956 $          5.18A56A
21/10/201957 $          1.29B57B
21/10/201957 $          6.35B57B
21/10/201957 $        13.77B57B
21/10/201960 $          2.14B60B
21/10/201963 $          3.72B63B
24/10/201964 $        17.73A64A
24/10/201965 $          7.52A65A
24/10/201965 $        11.42B65B
24/10/201968 $        17.09A68A
24/10/201969 $        17.59B69B
24/10/201970 $        12.34A70A
27/10/201971 $          2.99A71A
27/10/201971 $        19.44B71B
27/10/201973 $          5.41B73B
27/10/201975 $          3.82B75B
27/10/201977 $        10.57A77A
14/10/20201 $          8.55A1A
14/10/20202 $        19.38A2A
14/10/20203 $          3.77B3B
14/10/20203 $        14.97B3B
15/10/20206 $        16.82A6A
15/10/20207 $        16.04A7A
15/10/20208 $          0.42B8B
17/10/202025 $          6.08A25A
18/10/202038 $        16.38A38A
18/10/202039 $        12.19A39A
18/10/202040 $          5.35B40B
18/10/202041 $        16.50B41B
18/10/202042 $        17.14A42A
19/10/202043 $          9.22A43A
19/10/202044 $        16.95B44B
19/10/202045 $        14.92A45A
19/10/202046 $          0.70A46A
19/10/202047 $        10.00B47B
19/10/202048 $        13.62B48B
19/10/202049 $          0.43B49B
20/10/202055 $          1.75A55A
21/10/202061 $          5.54A61A
21/10/202062 $        16.10B62B
21/10/202062 $        13.75B62B
24/10/202064 $        18.23A64A
24/10/202070 $        15.43A70A

 

And my calendar data

CALENDAR_DATEC_YEARWEEKC_YEARC_WEEK
14/10/2019201942201942
15/10/2019201942201942
16/10/2019201942201942
17/10/2019201942201942
18/10/2019201942201942
19/10/2019201942201942
20/10/2019201942201942
21/10/2019201943201943
22/10/2019201943201943
23/10/2019201943201943
24/10/2019201943201943
25/10/2019201943201943
26/10/2019201943201943
27/10/2019201943201943
14/10/2020202042202042
15/10/2020202042202042
16/10/2020202042202042
17/10/2020202042202042
18/10/2020202042202042
19/10/2020202043202043
20/10/2020202043202043
21/10/2020202043202043
22/10/2020202043202043
23/10/2020202043202043
24/10/2020202043202043
25/10/2020202043202043
26/10/2020202044202044
27/10/2020202044202044
28/10/2020202044202044
29/10/2020202044202044

 

And here is the connection in Power BI

 
 
Total Sales LY = 
VAR CurrentWeek = SELECTEDVALUE(DateTable[C_WEEK])
VAR CurrentYear = SELECTEDVALUE(DateTable[C_YEAR])
RETURN

SUMX(
  FILTER(ALL(DateTable),
        DateTable[C_WEEK] = CurrentWeek && DateTable[C_YEAR] = CurrentYear -1),
[Total Sales])

 

But I"m not sure how to replicate for LY Count.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi echow 

    I think you have related your sales table and date table by calendar date column.

    Firstly add a Yearweek column in SalesTable.

     

    YearWeek = RELATED(DateTable[C_YEARWEEK])

     

    Due to you want to compare values(divide sales with count) in select year with last year, it is better to build a slicer table(Yearweek) which is not related to other tables.

     

    YearWeek = VALUES(DateTable[C_YEARWEEK])

     

    Then you can achieve your goal by measures.

     

    Sales in Bar = 
VAR _SelWeeknum =
    VALUES ( YearWeek[C_YEARWEEK] )
VAR _Sales =
    CALCULATE (
        SUM ( SalesTable[ Sales ] ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[Category] = MAX ( SalesTable[Category] )
                && SalesTable[YearWeek] IN _SelWeeknum
        )
    )
VAR _Count =
    CALCULATE (
        DISTINCTCOUNT ( SalesTable[MBR_NO] ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[Category] = MAX ( SalesTable[Category] )
                && SalesTable[YearWeek] IN _SelWeeknum
        )
    )
RETURN
    DIVIDE ( _Sales, _Count )
    LY Sales in Bar = 
VAR _SelWeeknum =
    ADDCOLUMNS ( VALUES ( YearWeek[C_YEARWEEK] ), "LY Weeknum", [C_YEARWEEK] - 100 )
VAR _LYWeeknum =
    SUMMARIZE ( _SelWeeknum, [LY Weeknum] )
VAR _LYSales =
    CALCULATE (
        SUM ( SalesTable[ Sales ] ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[Category] = MAX ( SalesTable[Category] )
                && SalesTable[YearWeek] IN _LYWeeknum
        )
    )
VAR _LYCount =
    CALCULATE (
        DISTINCTCOUNT ( SalesTable[MBR_NO] ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[Category] = MAX ( SalesTable[Category] )
                && SalesTable[YearWeek] IN _LYWeeknum
        )
    )
RETURN
    DIVIDE ( _LYSales, _LYCount )

     

    Result is as below.

    Select 202042 and 202043

    Select 202043

    You can download the pbix file from this link: File

     

    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. 

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    echow , for Week on Week, You can create a week rank in date tbale

    a new column

    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

    or
    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

     

    create measures like
    This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
    Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

     

    Power BI — Week on Week and WTD
    https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8

    • echow's avatar
      echow
      Helper II
      5 years ago

      amitchandak Thanks for the reply. I"m not sure how the above will able to solve what I have to the final outcome.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi echow 

    I think you have related your sales table and date table by calendar date column.

    Firstly add a Yearweek column in SalesTable.

     

    YearWeek = RELATED(DateTable[C_YEARWEEK])

     

    Due to you want to compare values(divide sales with count) in select year with last year, it is better to build a slicer table(Yearweek) which is not related to other tables.

     

    YearWeek = VALUES(DateTable[C_YEARWEEK])

     

    Then you can achieve your goal by measures.

     

    Sales in Bar = 
VAR _SelWeeknum =
    VALUES ( YearWeek[C_YEARWEEK] )
VAR _Sales =
    CALCULATE (
        SUM ( SalesTable[ Sales ] ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[Category] = MAX ( SalesTable[Category] )
                && SalesTable[YearWeek] IN _SelWeeknum
        )
    )
VAR _Count =
    CALCULATE (
        DISTINCTCOUNT ( SalesTable[MBR_NO] ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[Category] = MAX ( SalesTable[Category] )
                && SalesTable[YearWeek] IN _SelWeeknum
        )
    )
RETURN
    DIVIDE ( _Sales, _Count )
    LY Sales in Bar = 
VAR _SelWeeknum =
    ADDCOLUMNS ( VALUES ( YearWeek[C_YEARWEEK] ), "LY Weeknum", [C_YEARWEEK] - 100 )
VAR _LYWeeknum =
    SUMMARIZE ( _SelWeeknum, [LY Weeknum] )
VAR _LYSales =
    CALCULATE (
        SUM ( SalesTable[ Sales ] ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[Category] = MAX ( SalesTable[Category] )
                && SalesTable[YearWeek] IN _LYWeeknum
        )
    )
VAR _LYCount =
    CALCULATE (
        DISTINCTCOUNT ( SalesTable[MBR_NO] ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[Category] = MAX ( SalesTable[Category] )
                && SalesTable[YearWeek] IN _LYWeeknum
        )
    )
RETURN
    DIVIDE ( _LYSales, _LYCount )

     

    Result is as below.

    Select 202042 and 202043

    Select 202043

    You can download the pbix file from this link: File

     

    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. 

    • echow's avatar
      echow
      Helper II
      5 years ago

      Thanks Anonymous . That is awesome!

    • echow's avatar
      echow
      Helper II
      5 years ago

      HI Anonymous ,

       

      Could you also share with me the formula for the LY Count for the Sales table?

       

      Rgds,

      Eric

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi echow 

        I am so glad to help you solve your problem.

        You can download the pbix file from this link: File

         

        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.