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
ringovski
Helper II
Helper II

Cumulative Total in Card

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"

        )

ringovski_0-1662686081846.jpeg

 

(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

        )

    )

)

 

ringovski_1-1662686296586.jpeg

   

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

ringovski_2-1662686332722.jpeg

Hope this clear and thanks for any assistance.

 

1 ACCEPTED SOLUTION
v-xiaosun-msft
Community Support
Community Support

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.

vxiaosunmsft_0-1663053940577.png

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

vxiaosunmsft_1-1663053940587.png

 

vxiaosunmsft_2-1663053940588.png

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:

vxiaosunmsft_3-1663053940591.png

 

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.

View solution in original post

2 REPLIES 2
v-xiaosun-msft
Community Support
Community Support

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.

vxiaosunmsft_0-1663053940577.png

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

vxiaosunmsft_1-1663053940587.png

 

vxiaosunmsft_2-1663053940588.png

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:

vxiaosunmsft_3-1663053940591.png

 

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.

amitchandak
Super User
Super User

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

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.

Top Solution Authors