Forum Discussion

fahadakbar's avatar
fahadakbar
New Member
8 years ago
Solved

Previous Week Calculations

Hi Guys,   I am so stuck here, would really appreciate help   I have daily customer account cancellation (called Vol) data. I want to calculate the sum of previous weeks cancelations along with c...
Need Help
  • fhill's avatar
    fhill
    8 years ago

    OK, this needs a little explaination.  See some sample data I created based on your input (Screen Shot 1).  

     

    The same formula works based on FACT Vol values with dates, cities, Disctricts, etc.  (Screen shot 3)  ** BUT ** you can't summarize AT2_PW_VOL_2 b/c it's a GROUP calcualtion by Week already...  

     

    AT2_PW_VOI_2 = CALCULATE(SUM('Activity Table 2'[VOL]), FILTER(ALLSELECTED('Activity Table 2'), 'Activity Table 2'[Calc_Week] = (EARLIER('Activity Table 2'[Calc_Week]) - 1) && 'Activity Table 2'[District] =  EARLIER('Activity Table 2'[District])))

     

    Now the 'why' it works...  See my 2nd screen shot where i Un-Sumed VOL.  Even though '-43' is the correct SUM'ED Total for VOL for the previous week for ON WWW, -43 isn't a SUM'ed value it's really -43 OVER AND OVER AND OVER again for the whole week.  As such, 'SUM'ing this column again throws off your numbers.

     

    Side note!  I'm assuming you will want to do some form of Delta or % change calcuation.  But remember these are columns and not measures, so you have to use something like MAX or AVG with the AT2_PW_VOL_2 column not to throw off your numbers again.  Since EVERY entry in AT2_PW_VOL_2 for the week (by district) is the same, it really doesn't matter what you use when creating your follow-up Measures.  (AVG might be safer actually?)

     

    DELTA = SUM('Activity Table 2'[VOL]) - MAX('Activity Table 2'[AT2_PW_VOI_2])

     

    Hope this helps!

    FOrrest

     

    P.S.  A little ISBLANK logic on DELTA will keep your week 1 from looking wrong.  :-)

     

fhill's avatar
fhill
Resident Rockstar
8 years ago

This is a Custom Column (not measure) to display prior week && (and) break up the Districts.

FOrrest

 

PW_VOI_2 = CALCULATE(SUM('Activity Table'[VOI]), FILTER(ALLSELECTED('Activity Table'), 'Activity Table'[Week] = (EARLIER('Activity Table'[Week]) - 1) && 'Activity Table'[District] = EARLIER('Activity Table'[District])))

 

  • fahadakbar's avatar
    fahadakbar
    New Member
    8 years ago

    Hi Fhill,

     

    many many thanks,

     

    i used this approach earlier, but instead using && i just added another FILTER expression. 

     

    Anyhow, it still seems to have a problem, when i created a custom column using :

     

    Column = calculate (
    sum('Activity Table'[Vol]),
    filter(allselected('Activity Table'),'Activity Table'[Week]= earlier('Activity Table'[Week]) &&
    'Activity Table'[District] = EARLIER('Activity Table'[District])))

     

    the result is apparently showing my the sum of vol for the same week, not previous,

    see below :

     

    what am i doing wrong here?

     

    Thanks

     

    • fhill's avatar
      fhill
      Resident Rockstar
      8 years ago

      Make sure you include the 'Minus 1' ( Earlier....  -1 ) part to go back a week.  If this still doesn't work, can you post a sample of your raw data?  It could be a problem that VOI is hard coded for me, but i'm guessing your VOI is already a SUM value of VOI records? 


      FOrrest

      • fahadakbar's avatar
        fahadakbar
        New Member
        8 years ago

        Yes,

         

        i am including Earlier in the DAX expression for both(district & week)

         

        you are right, VOL is already a sum of week 

         

        (Measure)

        Vol total for Week =
        CALCULATE(SUM('Activity Table'[Vol]), ALL('Activity Table'[Week]))

         

        Originally, data sate is like this :

         

        Date,        Week ,Prov, District, City , VOL

        1/1/2017  ,1,        ON ,  XXX,      YYY ,  -1 

        1/2/2017  ,1,        PE,     CCC,      AAA. -1

         

        ** week is also a calculated number through weeknumber(date)