Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Use previous total with blank cumulative data

I am running aggregate totals based on GL for one of my reports. There is a gap in entries for a few periods where i would like to carry the previous periods amount if there were no transactions that period. Cant seem to get the logic after the initial cumulative code.  THanks

 

 

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous , The FiscalProfilePeriod should be a separate table like the date table. and period on axis should also come from FiscalProfilePeriod 

     

    and use all on table in place of table column

     

    filter(all(FiscalProfilePeriod ), <rest of the code >

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      those dates are from a profile that is fiscal period the date table is separate from them

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      i figured it out

       

       

       

  • PabloDeheza's avatar
    PabloDeheza
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hi there!

    For cumulative sum you try this:

    CALCULATE (
    SUM ( ColumnToSum ),
    FILTER (
        ALL ( DateTable ),
        DateTable[Date] <= MAX ( DateTable[Date] )
    )
)

    Let me know if that helps!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I am sorry i shouldve been more specific. I do not have an issue with the cumulative total rather when i try to break out the individual GLs and there is no entry for that specific period. This example shows me filtering the cumulative total and in period 6 there is no value so in the table it shows up as blank where i want it to show 27480.48 until period 7 where there is a new entry