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

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.