cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Patron

## 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

1 ACCEPTED SOLUTION
Community Champion

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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

11 REPLIES 11
Community Champion

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?

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Post Patron

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 )

Community Champion

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?

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Post Patron

Sorry for the bad explenation .

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

Community Champion

Please provide a depiction of what you are after

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Post Patron

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)

Community Champion

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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Post Patron

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 🙂

Community Champion

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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Community Champion

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))

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Post Patron

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors