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

Wresen's avatar
Wresen
Post Patron
3 years ago

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's avatar
PaulDBrown
Community Champion
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

  • Wresen's avatar
    Wresen
    Post Patron
    3 years ago

    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's avatar
    PaulDBrown
    Community Champion
    3 years ago

    Wresen 

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