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

      • calimero48's avatar
        calimero48
        Helper II
        8 years ago

        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