Measure to Total each Period Spent using Year to Date Spent for each service

[SushiSashimi1]

Trying to create a dashboard - part to show total spent per period.  Unable to as all spent amount are year to date spent per serivce, hence all values either doubled, tripled or more.. 

Is there a way to sum the total per period?

Example of data as per below:

Period No Service YTD Total Spent
Jul-21 A 10000
Aug-21 A 11000
Sep-21 A 11100
Oct-21 A 15000
Nov-21 A 15500
Dec-21 A 20000
Jul-21 B 599
Aug-21 B 1000
Sep-21 B 3000
Oct-21 B 3500
Nov-21 B 5000
Dec-21 B 9000
Jul-21 C 0
Aug-21 C 400
Sep-21 C 900
Oct-21 C 1500
Nov-21 C 2000
Dec-21 C 3000

Thank you

[v-yanjiang-msft]

Hi @SushiSashimi1 ,

According to your description, in my understanding, you only have YTD spend for each period and service, now you want the total spend for each period and service.

Actually the last YTD data in that period is the total of that period

If there're many years in your sample and you want to calculate total in each year, you can firstly create a custom column in Power Query.

Year = [Period No]

Then select the custom column and click Extract>Text After Delimiter.

Get the year column.

Then create a measure.

Measure =
MAXX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Service] = MAX ( 'Table'[Service] )
            && 'Table'[Year] = MAX ( 'Table'[Year] )
    ),
    'Table'[YTD Total Spent]
)

Get the result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[SushiSashimi1]

Thank you for your guidance and sorry for not being very clear of what I wanting to achieve.

I am using your pbix and put in additional visual note to explain: multi select - Jul & Aug, it will just sum both value together... maybe it's an easy fix but I just cant my head around it.. 

Total sum not correct:

Service A should be 11000, Service B should be 1000, Service B should be 400

[lbendlin]

What is the expected outcome based on your sample data?