I have a YTD calculation based on a custom fiscal year, and I want my measure to work at Day, Week, Month, Quarter and Year levels. Here (below) is what I have so far and it works fine.
This is OK in an environment where I control the date table fields that are ISINSCOPE. Clearly though, a date table has numerous fields at the various grains - e.g. Day Number, Day Name, Fiscal Year Day Number etc. I want my measure to “just work” regardless, as it may be consumed in a pivot table where I have no control over which fields are used.
So my question: Is there a better way of accomplishing this without checking the ISINSCOPE of all similar-grain fields?
Gross Sales YTD 2.0 =
SWITCH (
TRUE (),
ISINSCOPE ( ‘Date’[Fiscal Year Day Number] ) || ISINSCOPE (‘Date’[Day Of Week]),
CALCULATE (
[Gross Sales £],
FILTER (
ALL ( ‘Date’ ),
‘Date’[Fiscal Year] = SELECTEDVALUE ( ‘Date’[Fiscal Year] )
&& ‘Date’[Fiscal Year Day Number]
<= SELECTEDVALUE ( ‘Date’[Fiscal Year Day Number] )
)
),
ISINSCOPE ( ‘Date’[Fiscal Week] ),
CALCULATE (
[Gross Sales £],
FILTER (
ALL ( ‘Date’ ),
‘Date’[Fiscal Year] = SELECTEDVALUE ( ‘Date’[Fiscal Year] )
&& ‘Date’[Fiscal Week] <= SELECTEDVALUE ( ‘Date’[Fiscal Week] )
)
),
ISINSCOPE ( ‘Date’[Fiscal Month Name] ),
CALCULATE (
[Gross Sales £],
FILTER (
ALL ( ‘Date’ ),
‘Date’[Fiscal Year] = SELECTEDVALUE ( ‘Date’[Fiscal Year] )
&& ‘Date’[Fiscal Month Number] <= SELECTEDVALUE ( ‘Date’[Fiscal Month Number] )
)
),
ISINSCOPE ( ‘Date’[Fiscal Quarter] ),
CALCULATE (
[Gross Sales £],
FILTER (
ALL ( ‘Date’ ),
‘Date’[Fiscal Year] = SELECTEDVALUE ( ‘Date’[Fiscal Year] )
&& ‘Date’[Fiscal Quarter] <= SELECTEDVALUE ( ‘Date’[Fiscal Quarter] )
)
),
ISINSCOPE ( ‘Date’[Fiscal Year] ),
CALCULATE (
[Gross Sales £],
FILTER (
ALL ( ‘Date’ ),
‘Date’[Fiscal Year] = SELECTEDVALUE ( ‘Date’[Fiscal Year] )
)
),
BLANK ()
)