In this Prior Year-to-Date example, which is at the monthly grain, the business requirements are as follows:
- Filter the prior year by only the completed periods of the current year (only compare completed against completed)
- Calculate the Prior Year-to-Date value (completed periods only) automatically without any date columns in the report
- Return a blank if the current period, which is incomplete, is selected in report visuals
- Create a measure with multiple pairs of conditions and results to account for the filter contexts and requirements:
Sales (PYTD-Custom) = SWITCH(TRUE(),NOT(ISCROSSFILTERED('Date')), CALCULATE([Internet Sales],FILTER(ALL('Date'),'Date'[Calendar Year] = MAX('Date'[Calendar Year])-1 && 'Date'[Calendar ...
