Forum Discussion

Wresen's avatar
Wresen
Post Patron
3 years ago
Solved

Shifting data backwards

Hi and thanks for reading this.

I am not sure the title is correct but i was not sure what to call this.

 

I have two tables conected to each other and i have a mesure that calculate the sum for each day.

What i want is like and loop that changes the sum value for each day minus 1 (and do this 3 times)

like picture below.

I need to keep the model (its bigger then this) also if its possible i need to be able to have othere measure inbetween each loop -1 (hard to explain good i guess)

 

/Thanks so much 

 

https://drive.google.com/file/d/1VfMJgIduaoiDxqPGFxtLbZRaEkYtiJln/view?usp=sharing

 

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    OK, the measures you need are:

    For + 3 days

     

    +3 day = CALCULATE([Sum], FILTER(ALL(DTable), DTable[DayTable] = MAX(DTable[DayTable]) +3))
    Values at + 3 or less =
CALCULATE (
    [Sum],
    FILTER (
        ALL ( DTable[DayTable] ),
        DTable[DayTable] > MAX ( DTable[DayTable] )
            && DTable[DayTable]
                <= MAX ( DTable[DayTable] ) + 3
    )
)

    Aggregated total at +3 = SUMX(DTable, [Values at + 3 or less])

     

    and if you want to display the +3days value at day level and the aggregated total at the total level:

     

    Display at +3 = IF(ISINSCOPE(DTable[DayTable]), [+3 day], [Aggregated total at +3])

     

    So, for +18 days, you need:

     

    Values at + 18 or less =
CALCULATE (
    [Sum],
    FILTER (
        ALL ( DTable[DayTable] ),
        DTable[DayTable] > MAX ( DTable[DayTable] )
            && DTable[DayTable]
                <= MAX ( DTable[DayTable] ) + 18
    )
)

    Aggregated total at +18 = 
SUMX(DTable, [Values at + 18 or less])

     

    and if you want the +18 day values displayed ate Day level and the total aggragation at the total level:

     

    +18 day = CALCULATE([Sum], FILTER(ALL(DTable), DTable[DayTable] = MAX(DTable[DayTable]) +18))

     

     

    Display at +18 = 
IF(ISINSCOPE(DTable[DayTable]), [+18 day], [Aggregated total at +18])

     

    Sample file attached

11 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    Try:

    Sum - 1 day = CALCULATE ([Sum], FILTER(ALL(DayTable), DayTable[DayTable] = MAX(DayTable[DayTable]) -1))

     

    Sum - 2 day = CALCULATE ([Sum], FILTER(ALL(DayTable), DayTable[DayTable] = MAX(DayTable[DayTable]) -2))

     

    Sum - 3 days = CALCULATE ([Sum], FILTER(ALL(DayTable), DayTable[DayTable] = MAX(DayTable[DayTable]) -3))

    • Wresen's avatar
      Wresen
      Post Patron
      3 years ago

      Thanks PaulDBrown 

      It seems to work as i want if a do + instead of - in the end 😉

      Additinal question to this . Since this is a small example on what i need in the end , is it possible in some way to us like an For /Next loop to this (i cant see how i can do this without For/Next)
      ex.
      For i =1 to 3
      Sum - 1 day = CALCULATE ([Sum], FILTER(ALL(DayTable), DayTable[DayTable] = MAX(DayTable[DayTable]) +i))
      i=i+1
      Next i

      i dont need to see all the shiftings backwards but i needs to happen like your 3 formulas(so if i put it in a table i only see the last loop (+3) , but +1 and +2 has happend in the loop)
      Thanks so much.

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    Sorry, I don't really understand what you need. If you only need the result for +3, just use the measure for + 3. Why do you need it to loop? 

    • Wresen's avatar
      Wresen
      Post Patron
      3 years ago

      Hi PaulDBrown 

      Sorry for not beeing good in trying to explain.

      I want to do calculations in each minus shift and since there will be many shifts (18) i do not want to do a measure for each minus shift
      I am currently trying to do some sort of For /Next look (with help form an articlal here writen by Greg_Deckler )

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        3 years ago

        My question is: what do you need to display in the final visual? The +3 measure, or does it have to be dynamic based on a value the user selects from a slicer?