cancel
Showing results for
Did you mean:
Regular Visitor

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Senior Member

Re: Running total until Today

@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 11
Senior Member

Re: Running total until Today

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()))
)
Super Contributor

Re: Running total until Today

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

Re: Running total until Today

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.

Super Contributor

Re: Running total until Today

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

Re: Running total until Today

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

Senior Member

Re: Running total until Today

@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())
)
)
Regular Visitor

Re: Running total until Today

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

Senior Member

Re: Running total until Today

@mauroanelli

You need to substitute the column references in the measure with whatever equivalent columns you have in 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

Regular Visitor

Re: Running total until Today

still show the value on all dates

Announcements

Back to School Contest

Engage and empower students with Power BI!

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 110 members 1,638 guests
Recent signins: