Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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() )
Solved! Go to 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:
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@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.
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:
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
111 | |
96 | |
96 | |
67 | |
63 |