Forum Discussion

mauroanelli's avatar
mauroanelli
Helper I
8 years ago
Solved

Running total until Today

hi all , i use this formula for running total

rt Liquidità = CALCULATE(sum('Liquidità'[Liquidità]);FILTER((ALL('Calendar'[Data].[Date]));'Calendar'[Data].[Date] <= MAX('Calendar'[Data].[Date])))

but it gives value until the end of the period and if i fildar the date i gel the running total only for the selected dates.

i need the running total value stops at the current mont and returns the running total of all the columns beside date filter.

any ideas pls?

 

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    8 years ago

    mauroanelli

     

    I see, yes. Sorry about that. I've just tested the following measure and it will return the cumulative value and display it only on the current month (and year):

    Running total upto and including Today in current month (and year) =

VAR RunningTotal = CALCULATE(sum('Liquidità'[Liquidità]);
FILTER(ALL('Calendar');
'Calendar'[Date] <= TODAY())
)

RETURN
IF(MAX('Calendar'[year]) = YEAR(TODAY())  &&  MAX('Calendar'[month] ) = MONTH(TODAY()); RunningTotal ; BLANK())


    I hope that works. Sorry about the mistake.

    Regards,

    Paul.

11 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    8 years ago

    Hi mauroanelli

     

    I'm not sure if you need the total upto and including today's date or this month. Choose which meets your requirement:

    Running total upto and including Today

CALCULATE(sum('Liquidità'[Liquidità]);
FILTER(ALL('Calendar');
'Calendar'[Date] <= TODAY())
)
    Running total upto and including this month =

CALCULATE(sum('Liquidità'[Liquidità]);
FILTER(ALL('Calendar');
'Calendar'[Month] <= MONTH(TODAY()))
)
  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi mauroanelli,

     

    I guess you need to omit the ".[Date]" part in the formula that could make the measure ignore the context from the source table. Please try out this formula.

    rt Liquidità =
CALCULATE (
    SUM ( 'Liquidità'[Liquidità] );
    FILTER (
        ( ALL ( 'Calendar'[Data] ) );
        'Calendar'[Data] <= MAX ( 'Calendar'[Data] )
    )
)

    Best Regards,

    Dale

    • mauroanelli's avatar
      mauroanelli
      Helper I
      8 years ago

      thanks, the formula works fine itself.

      the problem is that i need that measure to sum with other in the same matrix.

      as it is i get the right value but for all the data (months). i need to have the value for all data until today and not further.

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi mauroanelli,

         

        Did you try PaulDBrown's solution? Maybe you can share the pbix file with us. You can delete the confidential parts first.

         

        Best Regards,

        Dale