Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All, I appreciate the help.
I'm using this YTD function:
Solved! Go to Solution.
I think I may have gotten this figured out thanks to @v-lili6-msft ! I modified your formula to this:
HI @Shelley,
You can modify your DAX expression as per below screenshot.
You can select any month from Slicer, the table visual will show total till that month only.
Or, may be you are looking for DATESMTD function. You can use
Thank you for the help, but I don't see how the formula you propose is different than mine? Also, it does the same as mine, it calculates YTD THRU the month selected, so if I have Dec selected, it calcs Oct, Nov and Dec; however, if Dec is selected, I only want Dec total to show. Likewise, if I have Dec and Jan selected, I want to see the two months added together - I don't want the full year to date value. Is there any way to do this?
HI, @Shelley
DATESYTD ( 'Date'[Date] ) Function corresponds to a filter over the date column using FILTER called by CALCULATETABLE, such as in the following code:
CALCULATETABLE ( FILTER ( ALL ( 'Date'[Date] ), AND ( 'Date'[Date] <= MAX ( 'Date'[Date] ), YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) ), ) ) ) )
So it will lead to your case, for your requirement, you need to try this way:
Whether there is a fiscal year column in your date table?
Year = YEAR(RA_Daily_Calendar[Date])
Month = MONTH(RA_Daily_Calendar[Date])
fiscal year = IF(RA_Daily_Calendar[Month] IN{1,2,3,4,5,6,7,8,9},RA_Daily_Calendar[Year]-1,RA_Daily_Calendar[Year] )Then use this formula to add a ytd measure
YTD Total Order $ Converted = CALCULATE ( [Total Order $ Converted], FILTER ( ALLSELECTED ( 'RA_Daily_Calendar' ), AND ( 'RA_Daily_Calendar'[Date] <= MAX ( 'RA_Daily_Calendar'[Date] ), RA_Daily_Calendar[fiscal year] = MAX ( RA_Daily_Calendar[fiscal year] ) ) ) )
Best Regards,
Lin
@v-lili6-msft Thank you for the very thorough response. This is so close. We do have our fiscal year in our calendar. So this is the formula I've used as you suggested.
I think I may have gotten this figured out thanks to @v-lili6-msft ! I modified your formula to this:
hi, @Shelley
It's pleasant that your problem has been solved, could you please mark my reply as Answered too?
Best Regards,
Lin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |