Forum Discussion
Impactful IndividualTOTALYTD Per Day Name
Hello,
I am scratching my head with the TOTALYTD calculations, and it doesn't seem to be working as intended. I have a Date Dimension table, which has dates and Day Names (Monday, Tuesday, etc.)
For whichever reason, the TOTALYTD doesn't seem to work when the minimum date of the slicer is shifted. I want the YTD calculation to work regardless of the minimum date.
This is what the table currently looks like
and when shifting the minimum date, I want the results for the FYTD column to stay the same, whereas the "Available minutes" is working as expected.
Using the TOTALYTD dax doesn't work, as shown below. It doesn't take the Day Name context into account.
The following is the most recent DAX. I have tried multiple things to no avail. Any help is appreciated!
FYTD_Available Minutes =
VAR _FirstFiscalDay = MIN('Date Dimension'[FiscalFirstDayOfYear])
VAR _ToDate = MAX('Date Dimension'[Date])
RETURN
CALCULATE([Measure],FILTER('Date Dimension','Date Dimension'[Date]>=_FirstFiscalDay && 'Date Dimension'[Date]<=_ToDate))Hi ExcelMonke
Would you be able to provide a sample dataset? And possibly with expected output?
9 Replies
hnguy71Super User
Hi ExcelMonke ,
Try to use CALCULATE without FILTER. The FILTER modifier strips all current evaluations and returns a table value which has been filtered.
You can also debug and return your FirstDate and ToDate for each of the days and see if you get the right dates in context.
ExcelMonkeHi hnguy71 ,
Thanks for the suggestion, but the result gave me the same result as the 3rd screenshot (>300K per day). I even tried adding in ALLEXCEPT and ALLSELECTED functions, but they didn't help. Do you have any other ideas?
Thanks!
v-saisrao-msftCommunity Support
Hi ExcelMonke ,
I wanted to check if you had the opportunity to review the information provided by hnguy71 . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
- hnguy71
Hi ExcelMonke ,
You want the FYTD measure to stay static for that fiscal year in question, right? You should be able to strip the date context using a variation of ALL from both your _FirstFiscalDay and _ToDate. I would also most likely use the _ToDate as my point of reference.
For example:
// return max date in current context based on all selected dates VAR _MaxDateInContext = CALCULATE(MAX('Table'[Date]), ALLSELECTED('Table')) // from the max date, find my min date for the current year VAR _MinFiscalDate = CALCULATE( MIN('Table'[Date]), KEEPFILTERS('Table'[Date] = _MaxDateInContext), ALL('Table')) RETURN _MinFiscalDate & " - " & _MaxDateInContextThat way, if I change my starting date, my minimum fiscal date will always stay the same and my max date will always be my max date within my dataset:
Do you have sample data or pbix that you can share with expected results?
- v-saisrao-msft
Hi ExcelMonke,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
- ExcelMonke
Hello v-saisrao-msft ,
Sorry for the late reply - hnguy71 I have not been able to get this to work still unfortunately- hnguy71
Hi ExcelMonke
Would you be able to provide a sample dataset? And possibly with expected output?
- v-saisrao-msft
Hi ExcelMonke,
May I ask if you have resolved this issue? If not, please provide the sample data and expected output so we can address the issue effectively.
Thank you.
- v-saisrao-msft
Hi ExcelMonke,
Could you please provide the sample data and expected output so that we can address the issue?
Thank you.