topic Cumulative Total in Card in DAX Commands and Tips

Cumulative Total in Card

ringovski — Fri, 09 Sep 2022 01:27:38 GMT

Hi All,

I would like to show the cumulative total waste in card for the month selected in the slicer. I already the cumulative(running total) working in a table, but in a card it only shows the current month. I've tried DATESYTD but it doesn't work, thanks.

(correct) # Total Waste =

CALCULATE (SUM('Metric'[Numeric Value])

,'Metric Name'[MetricName] = "Total General Waste recycled"

)

CALCULATE (SUM('Metric'[Numeric Value])

,'Metric Name'[MetricName] = "Total General Waste sent to landfill"

)

(correct) # Total Waste Running Total =

CALCULATE(

[# Total Waste],

FILTER(

CALCULATETABLE(

SUMMARIZE(

'Previous Date',

'Previous Date'[Month],

'Previous Date'[Month Short Name]

ALLSELECTED('Previous Date')

ISONORAFTER(

'Previous Date'[Month], MAX('Previous Date'[Month]), DESC,

'Previous Date'[Month Short Name], MAX('Previous Date'[Month Short Name]), DESC

)

Card measure attempt:

CALCULATE (SUM('Metric'[Numeric Value])

,'Metric Name'[MetricName] = "Total General Waste recycled"

,DATESYTD('Calendar'[Date])

)

CALCULATE (SUM('Metric'[Numeric Value])

,'Metric Name'[MetricName] = "Total General Waste sent to landfill"

,DATESYTD('Calendar'[Date])

)

Selected April in card should be 124.2 + 65.4

Hope this clear and thanks for any assistance.

Re: Cumulative Total in Card

amitchandak — Fri, 09 Sep 2022 04:37:12 GMT

ringovski , The datesytd should work. The calendar should be marked as a Date table. The slicer should use month year from calendar and join should be single directional

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Re: Cumulative Total in Card

v-xiaosun-msft — Tue, 13 Sep 2022 07:27:20 GMT

Hi ringovski ,

According to your description, here is my solution.

I created a sample directly from the “Month Short Name” and “Total Waste” you gave.

Create a DATE table and put it into slicer. Note that the relationship between the two dates should be established.

Create a measure and put it into a card.

Numeric Value = CALCULATE ( SUM ( 'Table'[Total Waste] ), DATESYTD ( 'Table'[Month Short Name] ) )

Select the month you need and you will get the final output:

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.