Prior week sales for week 1.

[Anonymous]

Hi,

Can someone help me with the Dax code;

I trying to write Dax for Prior Week Sales; I have the code and it is working for all weeks except for First week of the year; How do I get the Prior week sales for first week. The tricky part for me is , the prior week can be 53 rd or 52 nd week depending on the number of weeks last year had. Any help is appreciated. Thanks!!

PRIOR WTD = 
    IF(HASONEVALUE('Dim Date' [Year Number])
       && HASONEVALUE('Dim Date' [Week Number]),
            CALCULATE( Sum('Fact Daily Sales'[Sales]),
                    FILTER( ALL('Dim Date'),
                    'Dim Date'[Year Number]=VALUES('Dim Date'[Year Number])
                     && 'Dim Date'[Week Number]= Values('Dim Date'[Week Number])-1
                     && 'Dim Date'[Calendar Date]<=Max('Dim Date'[Calendar Date])
                          )
     ) ,
     BLANK()
    )   

[v-yuta-msft]

@Anonymous,

---

I trying to write Dax for Prior Week Sales; I have the code and it is working for all weeks except for First week of the year; How do I get the Prior week sales for first week. The tricky part for me is , the prior week can be 53 rd or 52 nd week depending on the number of weeks last year had. Any help is appreciated. Thanks!!

PRIOR WTD = 
    IF(HASONEVALUE('Dim Date' [Year Number])
       && HASONEVALUE('Dim Date' [Week Number]),
            CALCULATE( Sum('Fact Daily Sales'[Sales]),
                    FILTER( ALL('Dim Date'),
                    'Dim Date'[Year Number]=VALUES('Dim Date'[Year Number])
                     && 'Dim Date'[Week Number]= Values('Dim Date'[Week Number])-1
                     && 'Dim Date'[Calendar Date]<=Max('Dim Date'[Calendar Date])
                          )
     ) ,
     BLANK()
    )   

---

Could you share some sample data and clarify more details about your requirement?

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LivioLanzo · Accepted solution]

Hi @Anonymous,

you need to add to your calendar table a column where week numbers do not reset at the start of each year, i think it should be in this post I answered:

https://community.powerbi.com/t5/Desktop/Get-weekly-change-first-hour-in-week-compared-to-previous-weeks/m-p/600111#M285462