Same week previous year

Question

Hi&nbsp;I have create a measure to get the sum of sale from previous year same week as&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; IF ( HASONEVALUE (DAT[CalendarYear]), CALCULATE (SUM ([CA]),&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;FILTER (ALL ( DAT),&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; DAT[CalendarYear] = VALUES ( DAT[CalendarYear] ) - 1 &nbsp;&nbsp; &nbsp;&amp;&amp; CONTAINS(&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;VALUES ( DAT[WeekNumberOfYear] ),&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; DAT[WeekNumberOfYear],&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; DAT[WeekNumberOfYear] ))),BLANK())&nbsp;This measure works well until we drill down. For exemple, if I want to split to sales to product and in one week of the current year we do not have any sales, the measure do not return any previous sales.&nbsp;What I am doing wrong? Thanks in advance for your helps&nbsp;Regards&nbsp;&nbsp;

calimero48 · Answer

Sorry,&nbsp;I forgot the give the DAT table structureCalendarYear Integer&nbsp; -&gt; YearWeekNumberOfYear&nbsp; -&gt; Integer -&gt; Week number (1 to 53)FullAlternateDate -&gt; DateMonthNumber Integer -&gt; Month&nbsp;at least&nbsp;

v-chuncz-msft · Answer

calimero48,
&nbsp;
Check if the following link helps.
https://www.sqlbi.com/articles/compare-equivalent-periods-in-dax/

calimero48 · Answer

Hi&nbsp;Thank you for your reply. I start with this article.&nbsp;Let me explain :&nbsp;Fact table isDate|Vendor|Manager|Article|Value2017-01-04|v01|M01|A01|102017-01-11|v02|M01|A01|202017-01-05|v02|M01|A01|102018-01-24|v01|M01|A01|102018-01-12|v02|M01|A01|10&nbsp;&nbsp;With my&nbsp; measure if i doweek / Manager/vendor&nbsp; Previous year same week /Current year week&nbsp;I gotW1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2018&nbsp;&nbsp;&nbsp; A01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; V01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; ( I do not have sales in 2018 week 1)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; V01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; 10W2&nbsp;&nbsp;&nbsp; A01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; V02 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10 ( I&nbsp; have sales in 2018 week 2)&nbsp;&nbsp;What i understand is that my measure for the previous week year is taking only week of the current year having sales.&nbsp;How to avoid this?&nbsp;Thanks for your help

calimero48 · Answer

Hi&nbsp;I have read Marco Russo articlehttps://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/&nbsp;What I found, it's when I do not have any value on the current year/week, the measure is not calculated for the previous year.&nbsp;How to have it working ?&nbsp;Regards&nbsp;

calimero48 · Answer

Hi&nbsp;To go further in the debug,&nbsp; I found that if there is not data for the current year, VALUES ( DAT[WeekNumberOfYear]) return blank.&nbsp;How can I have the selected weekNumber returned even if there is no data for the current year.&nbsp;Thanks in advance for your help&nbsp;&nbsp;

Forum Discussion

Same week previous year

7 Replies

Related content

Calculate previous week total and previous 2 weeks total

Previous Week DAX

Calculate previous week values

Previous Week Sales

Count current week and previous week