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

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

  • mauroanelli's avatar
    mauroanelli
    Helper I
    8 years ago

    sure, works fine as yours but same problem.

    show the correct value but for all the dates, i just need to show it in current month

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      8 years ago

      mauroanelli

       

      If you only want the values to be visible in the current month (and year), try:

       

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

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

RETURN
CALCULATE (RunningTotal;
FILTER ('Calendar';
'Calendar'[Month] = MONTH(TODAY())
      &&
      'Calendar'[Year] = YEAR(TODAY())
      ) 
      )
      • 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