Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to 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~
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
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~
User | Count |
---|---|
66 | |
45 | |
20 | |
19 | |
15 |
User | Count |
---|---|
121 | |
41 | |
38 | |
28 | |
23 |