Forum Discussion

calimero48's avatar
calimero48
Helper II
8 years ago

Same week previous year

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 ( D...
Show and Tell
calimero48's avatar
calimero48
Helper II
8 years ago

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's avatar
      calimero48
      Helper II
      8 years ago

      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's avatar
        MojoGene
        Post Patron
        8 years ago

        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.)