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]))) bu...
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.

mauroanelli's avatar
mauroanelli
Helper I
8 years ago

it return an error

mdxScript(Model)(44,1) calculation error in the measure. DAX comparison operations do not support the text type comparison with Integer type values. Try using the VALUE or FORMAT function to convert one of the values

 

PaulDBrown's avatar
PaulDBrown
Community Champion
8 years ago

mauroanelli

 

You need to substitute the column references in the measure with whatever equivalent columns you have in your calendar table:

'Calendar' = <<your calendar table>>

'Calendar' [Month] = the column which is the Month as an integer in your Calendar table

'Calendar' [Year] = the column which is the Year as an integer in your Calendar table

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

  • mauroanelli's avatar
    mauroanelli
    Helper I
    8 years ago

    still show the value on all dates

  • mauroanelli's avatar
    mauroanelli
    Helper I
    8 years ago

    it works fine

    thank you very much