Forum Discussion

fsalinass's avatar
fsalinass
Frequent Visitor
7 years ago

Sum acumulated

Hi!
I have a question a little complex, I have a base for weeks (can not have for days) of 3 full years, each week has assigned a number of days (for example the week 51 is assigned with 52304 days), I need for each week to sum a year to the back, that is, for week 51 sum from week 52 of 2017 to week 51 of 2018, and so on for all the weeks of the last 2 years. Any ideas?

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    Sample data would really assist here along with your expected output. Probably can get there with some variation on my Time Intelligence The Hard Way Quick Measure, but would need to see how your data is laid out. So, for example, do your week numbers range from 0-53 and you have a year column as well or are they assigned 0-150(ish)? What do the assigned number of days mean, is that cumulative or something?

    • fsalinass's avatar
      fsalinass
      Frequent Visitor
      7 years ago

      The days is like a random data, and is not acumulative.

       

      Here is a sample data:

       

      Year_dpMonth_dpWeek-DPdaysbyweekSede
      20173130a
      201731312b
      20173140c
      201731220d
      201731019d
      20173110b
      20173140a
      201731313b
      20173100c
      20173130d
      201731449d
      201731025b
      201731325a
      20173140b
      20173111c
      20173110d
      20173130d
      20173120b
      201731424a
      20173110b
      20173110a
      20173120b
      20173120c
      20173110d
      201731314d
      20173110b
      20173100a
      20173120b
      20173100c
      20173130d
      20173130d
      20173100a
      20173120b
      20173110c
      20173100d
      20173140d
      201731336b
      20173140a
      20173110b
      20173100c
      20173130d
      20173120d
      20173110a
      201731412b
      20173120c
      20173110d
      20173120d
      20173110b
      20173140a
      20173120b
      20173100c
      20173110d
      20173130d
      201731329a
      20173120b
      20173100c
      20173130d
      20173110d
      20173110b
      20173140a
      20173120b
      2017312165c
      20173100d
      20173100d
      20173100a
      20173140b
      20173120c
      20173130d
      20173120d
    • fsalinass's avatar
      fsalinass
      Frequent Visitor
      7 years ago

      Hi!

       

      The number days is not acumulative, is like a random number for eachs week.

       

      Here is a data example:

       

      Year   Month   Week   days   Sede

      2017      7          42      254      a

      2017      7          42      2          b

      2018      8          44      13        c

      2016      7          42      15        a

      2016      8          44      24        a

      2018 7 43 0 b

       

      So, for each week i have to sum the Days of the 52 before week for all the "Sedes".

      For week 41 2018 i need to sum the days from week 42 2017 to week 41 2028

      For week 35 2018 i need to sum the days from week 36 2017 to week 35 2028

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        HI fsalinass,

         

        I write a measure to calculate total 'daysofweeks' based on current year, week and grouped by sede, maybe you can try it if it works.

        Measure =
VAR currYear =
    MAX ( Table1[Year_dp] )
VAR currWeek =
    MAX ( Table1[Week-DP] )
RETURN
    CALCULATE (
        SUM ( Table1[daysbyweek] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            OR (
                [Year_dp] = currYear
                    && [Week-DP] <= currWeek,
                [Year_dp]
                    = currYear - 1
                    && [Week-DP]
                    > currWeek - 1
            )
        ),
        VALUES ( Table1[Sede] )
    )


        If above not help, please share more detail information about your requirement with expected results.

         

        Regards,

        Xiaoxin Sheng