YTD as calculated column instead of measure


#1

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.


#2

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

Enterprise%20DNA%20Expert%20-%20Small


#3

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.


#4

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

Enterprise%20DNA%20Expert%20-%20Small


#5

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.


#6

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.


#7

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.

Enterprise%20DNA%20Expert%20-%20Small


#8

Thanks Paul, the question is - could YTD be also used to reflect similar behaviour. Any direction, thought is appreciated.


#9

Suggest you indeed share the pbix. It is always hard to imagine someone else’s model.

Enterprise%20DNA%20Expert%20-%20Small