Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
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.
Proud to be a Super User!
Paul on Linkedin.
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
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
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
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())
)
)
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
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
Proud to be a Super User!
Paul on Linkedin.
still show the value on all dates
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.
Proud to be a Super User!
Paul on Linkedin.
it works fine
thank you very much
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())) )
Proud to be a Super User!
Paul on Linkedin.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |