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

Sorry for the bad explenation .

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

PaulDBrown's avatar
PaulDBrown
Community Champion
3 years ago

Please provide a depiction of what you are after

  • 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 🙂