Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mauroanelli
Helper I
Helper I

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

@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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

11 REPLIES 11
v-jiascu-msft
Employee
Employee

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.

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.

 

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.

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

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




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

 

@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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






still show the value on all dates

@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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






it works fine

thank you very much

PaulDBrown
Community Champion
Community Champion

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




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.