Shifting data backwards

[Wresen]

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

[PaulDBrown]

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]

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]

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?

[Wresen]

Sorry for the bad explenation .

In the last +3 the total value should be shown (added form +1 ,+2 and +3)

[PaulDBrown]

Please provide a depiction of what you are after

[Wresen]

Hi

I want it in the end to add all the values togather as a total value

That is why a talk about a For / next loop in some sort of way since i need in the end to do this back shifting 18 times to get the final value (is this example where we shift back total 3 times with help of your measures its 132)

[PaulDBrown · Accepted solution]

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

[Wresen]

Hi @PaulDBrown 

I would like to say a big thanks for all the help here. I really appriciate it and i am sorry that i was bad in explaining what i wanted .

Again Thanks so much 🙂 

[PaulDBrown]

@Wresen 

No need to apologise! I'm glad we got there in the end

[PaulDBrown]

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]

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.