In a calendar date dimension table, it is possible to create Financial Year, Financial Month and Quarter.
Is it possible to have a YTD or Fiscal YTD column? Currently only able to do it on a measure using TOTALYTD or similar functions.
Kindly suggest.
Only way you would need these columns is if you a referring to the “Date”. You can create these calculated columns very easily. That should be only reason for a calculated column for each of these fields. Measures are used for best practice to get totals. As a best practice, avoid calculated columns unless it is necessary.
Cheers
Jarrett
Pardon me, but still does clarify about YTD as a column. I need to show it in a slicer along with Quarter as a selectable value on which measure will be calculated.
No sure you understood what i was talking about. Not saying it is NOT needed, just don’t fully understand why you need to have a calculated column in your example. If you watch any of Sam’s video’s, he never refers to YTD as a calculated column, always as a measure. It is one of his best practices. Please provide a sample PBIX file of data so that we can better help resolve your question.
Thanks
Jarrett
PRA; Maybe I misunderstood, but you would probably need to do it in the fact table as a calculated column: YTD= TOTALYTD(SUM(Facttable[amount]);Calendar[Date]) This column you can use in visuals and slicers like any other column.
Thanks, I am fully in sync with Sam’s idea if YTD and about creating a date table, whereby I get the flexibility to create financial columns for entire hierarchy. But the requirement is unique - the user wants to be able to select Fiscal Quarters (Q1, Q2, Q3, Q4) or YTD and see how revenue changes. So in a way I need to have a matrix with one level for financial year and next level as Quarter+YTD for selected financial year(s). If I make it as a table, and show this as measure, then for situation where multiple financial years are selected (for sake of comparison) I would need to create measures based on each quarter and YTD. I tried to create a disconnected slicer with hard coded values as Q1, Q2, Q3, Q4, YTD, so that using SELECTEDVALUES I, I capture the choice and update the measure (revenue) basis that. But I am not sure that’s the best way, or is performance optimal. kindly advice. I will share the pbix with appropriate data shortly for better visibility.
Surely you can use a Financial Quarter column in your date table, assuming a relation ship between the date table and the fact table exists and build your measures. The Q column can be used in a slicer, so you can select 1 or more quarters.
Thanks Paul, the question is - could YTD be also used to reflect similar behaviour. Any direction, thought is appreciated.
Suggest you indeed share the pbix. It is always hard to imagine someone else’s model.