cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mauroanelli Regular Visitor
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
PaulDBrown Senior Member
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
PaulDBrown Senior Member
Senior Member

Re: Running total until Today

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 Super Contributor
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.
mauroanelli Regular Visitor
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.

 

v-jiascu-msft Super Contributor
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.
mauroanelli Regular Visitor
Regular Visitor

Re: Running total until Today

sure, works fine as yours but same problem.

pbi.JPG

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

PaulDBrown Senior Member
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())
)
)
mauroanelli Regular Visitor
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

 

PaulDBrown Senior Member
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' = <<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

mauroanelli Regular Visitor
Regular Visitor

Re: Running total until Today

still show the value on all dates

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 110 members 1,638 guests
Please welcome our newest community members: