I have a large tabular model with hundreds of measures, many of which require time aggregations such as YTD. I am familiar with Russo & Ferrari's DAX Patterns related to time functions however I'm now faced with constructing a YTD measure (for example) for each measure in the model where it's required. I've successfully written expressions for CYTD, year-over-year, PYTD, MTD, etc. but only when they are specific to a particular measure. This is a copyandpaste job for me but results in the users having to traverse DisplayFolder structures to house all extensive CYTD, PYTD, and other calculated time-aggregated measures. What I cannot fathom is how to build a DAX expression that is agnostic about the measure being referenced. Specifically, instead of a DAX expression that specifies a single measure, such as:
[YTD Sales Units] := CALCULATE([Sales Units], FILTER(...))
I'd like to build a generic expression in, say, my Time dimension that looks like:
[YTD] := CALCULATE([measures].CURRENTMEMBER, FILTER(...))
I've built these in MDX in my multi-dimensional models to great effect, but I can't find the DAX equivalent. It appears to be clear from TechNet and the folks at SQLBI that there are workarounds possible when traversing hierarchies and the current row/column filter context but I'm too new to DAX to be able to translated this to measures.
The goal is, for example, to permit the users to filter on the a year and month, select the [Sales Amount], [Sales Units] and [Gross Margin] measures and the single [YTD] member.
I've tried the technique of creating a time aggregation member table where the user sees this as, say, a slicer and a massive set of conditional tests in my expression attempt to pick out the correct function however this works best for interactive dashboards and not for general reporting.
Lastly, I've even tried designing Time dimension attributes that group dates, such as [YTD(Jul)], [YTD(Aug)], etc. which works well for a cube with monthly granularity, but not so much with my daily or weekly cubes.
Is there a "best practice" or commonly used technique for a measures-agnostic time aggregation or do I really have to just create an expression for each measure?
