Calculating YTD using a month name slicer

[regnidem16]

I'm not really sure how to best summarize what I'm trying to do.

I have a month name slicer.  I'm attempting to create two measures, Prior Year YTD and Current Year YTD., so that when the user selects one of the months in the slicer it calculates the YTD up to that month.  For example, using the data in the table below, if January was selected in the slicer, the result for the previous year should be 244744 and for the current year it would be 23979. If February was select, then for the previous year the result would be 438124 and for the current year it would be 23979. If March was selected, the result for the previous year would be 485285 and for the current year it would be 41150.

Invoice Date	InvoiceLineAmount	Segment
3/29/2024	47487.49	J. GLP Revenue
3/29/2024	5618.92	J. GLP Revenue
3/29/2024	85135.14	J. GLP Revenue
3/29/2024	9237.16	J. GLP Revenue
3/29/2024	4256.76	J. GLP Revenue
3/29/2024	21719.13	J. GLP Revenue
3/29/2024	26391.89	J. GLP Revenue
3/29/2024	19979.34	J. GLP Revenue
3/29/2024	23160.9	J. GLP Revenue
3/29/2024	186128.08	J. GLP Revenue
3/29/2024	15421.77	J. GLP Revenue
3/29/2024	9990	J. GLP Revenue
3/29/2024	275	J. GLP Revenue
3/29/2024	-198086	J. GLP Revenue
3/29/2024	-245738	J. GLP Revenue
3/29/2024	-370.5	J. GLP Revenue
3/29/2024	6564.16	J. GLP Revenue
2/28/2023	63702.18	J. GLP Revenue
2/28/2023	30331.76	J. GLP Revenue
1/25/2023	228133.4	J. GLP Revenue
3/31/2023	47160.88	J. GLP Revenue
2/28/2023	99346.04	J. GLP Revenue
1/31/2023	16611.42	J. GLP Revenue
1/31/2024	23979.63	J. GLP Revenue

I tried modifying my YTD measure to achieve this, but have not been successful.  The closest I've gotten (with chatGPTs help) is 

Previous Year YTD =
VAR SelectedMonth = SELECTEDVALUE('Date'[Month Name])
RETURN
CALCULATE(
TOTALYTD(
SUM('Data'[InvoiceLineAmount]),
'Date'[Date]
),
'Date'[Month Name] = SelectedMonth,
ALL('Data'),
SAMEPERIODLASTYEAR('Date'[Date])
)

however, it's simply returning the same value for each different revenue segment.

Does anyone have any insight on how to acheive my goal?

[Anonymous]

Hi @regnidem16 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[xifeng_L · Accepted solution]

Hi @regnidem16 ,

You need to create a calendar table and establish a one to many relationship with the fact table.

Then, use the measures of below:

Current Year YTD = CALCULATE(SUM('Fact'[InvoiceLineAmount]),DATESYTD('Calendar'[Date]))

Previous Year YTD = CALCULATE(SUM('Fact'[InvoiceLineAmount]),DATESYTD(SAMEPERIODLASTYEAR('Calendar'[Date])))

The result as follow:

For the convenience of displaying the results of different options in the slicer, I have placed the month field in the matrix.

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~