Same week previous year

[calimero48]

Hi

I have create a measure to get the sum of sale from previous year same week as

        IF ( HASONEVALUE (DAT[CalendarYear]), CALCULATE (SUM ([CA]),    
                    FILTER (ALL ( DAT),
                            DAT[CalendarYear] = VALUES ( DAT[CalendarYear] ) - 1     && CONTAINS(
                            VALUES ( DAT[WeekNumberOfYear] ),
                            DAT[WeekNumberOfYear],
                            DAT[WeekNumberOfYear] ))),BLANK())

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.

What I am doing wrong? Thanks in advance for your helps

Regards

[v-chuncz-msft]

@calimero48,

Check if the following link helps.

https://www.sqlbi.com/articles/compare-equivalent-periods-in-dax/

[calimero48]

Hi

Thank you for your reply. I start with this article.

Let me explain :

Fact table is

Date|Vendor|Manager|Article|Value

2017-01-04|v01|M01|A01|10

2017-01-11|v02|M01|A01|20

2017-01-05|v02|M01|A01|10

2018-01-24|v01|M01|A01|10

2018-01-12|v02|M01|A01|10

With my  measure if i do

week / Manager/vendor  Previous year same week /Current year week

I got

W1                  2017         2018

    A01              20             10

            V01                            ( I do not have sales in 2018 week 1)

            V01                       10

W2

    A01               20           10

            V02       20           10 ( I  have sales in 2018 week 2)

What i understand is that my measure for the previous week year is taking only week of the current year having sales.

How to avoid this?

Thanks for your help

[calimero48]

Hi

I have read Marco Russo article

https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/

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.

How to have it working ?

Regards

[calimero48]

Hi

To go further in the debug,  I found that if there is not data for the current year, VALUES ( DAT[WeekNumberOfYear]) return blank.

How can I have the selected weekNumber returned even if there is no data for the current year.

Thanks in advance for your help

[MojoGene]

Here's a thought.

If you divide the sales figue by itself you will come up with one of two values: 1 or 0 (because anything divided by itself will be 1 and anything divided by zero will be zero). Then multiple the resulting value by your full measure. The result will be either 1 * [MEASURE] or 0 * [MEASURE].

This might return the sales in the current week having no sales as zero as you seem to require. (Sorry I haven't tested this.)

[calimero48]

Hi

Thanks for your suggestion but my problem is that the measure of the previous year same week does not give the correct result.

the measure "previous year same week" does not include all fact table of the previous year week if, there is no sales in the current year.

Regards

[calimero48]

Sorry,

I forgot the give the DAT table structure

CalendarYear Integer  -> Year

WeekNumberOfYear  -> Integer -> Week number (1 to 53)

FullAlternateDate -> Date

MonthNumber Integer -> Month

at least