Tabular Editor: Non-Standard Calendar time comparisons/Conditional calculation with filters

[Dichilyn1]

Hi All, 

I am looking to transition from creating redundant time comparison measures (that require a lot of copy and pasting for measure creation) to utilizing tabular editor. 

A couple of callouts:

NonStandard calendar (I map all data to another table that translates)

Weekly data (a lot of formulas based on days will skew data)

A lot of different business measures that require different calculation types (example consumption (sumx), inventory (averaged), time supply (sum and averaged))

I was blown away by the ability to create a time dimension in tabular editor like last week and have it work for all measures, but as I began grouping weeks, I realized the output wouldn't be correct for all measures unless my DAX got more complex. This is what I need help with, as I've kept my measures faily simple in the past:

Below is the mess I have going on now in tabular editor. 

I created a variable that identifies the time period I want to filter to, but from here I want to add an if statement that will sum some measures, average others, and sum and other combined measures. I'm getting syntax errors on "Divide" which would be the calculation type if the selected measure IS one of the ones listed in the bracket....I haven't even made it far enough to attempt the "if not" section. 

Please save me. 

[julioverdugo]

Based on your screenshot, you need to replace the SELECTEDMEASURE() right after the CALCULATE statement with the DIVIDE statement below. 

Let me know if this solves the issue.

Best,

JV.

[Dichilyn1]

The error now says Error on L13Wk Expression: The end of the expression was reached.

[julioverdugo]

Try this: 

VAR CurrWMWK =

    SELECTEDVALUE( 'Calendar'[WM Chronological Order])

RETURN

   IF(

        ISSELECTEDMEASURE([OH Qty], [OH Cost], [POS Qty]),

        CALCULATE(

            DIVIDE( SELECTEDMEASURE(), COUNTROWS('Calendar')), --or the calendar table you need

            FILTER(

                 ALL('Calendar'),

                 'Calendar'[WM Chronological Order] <= CurrWMWK - 1

                 && 'Calendar'[WM Chronological Order] > CurrWMWK - 14

           )

        )

    )

[Dichilyn1]

It accepted the DAX, but the output doesn't seem to be reacting to the if statement and the values are not as expected

[julioverdugo]

You may need to adjust the filters for your period definition. 

Can you provide a screenshot of your calendar table to understand the mechanic behind it and explain in more detail what is the expected output from the calculation?

[Dichilyn1]

It is not an issue with the calendar or time portion, as the formula stemmed from a summation of the L13wks (which I validated before adding to the dax to apply the conditional filter). 

[julioverdugo]

I recommend you troubleshoot it piece by piece. You can create two additional calculations to pull only the Selected measure and another one for COUNTROWS just to validate what they are returning.

Are all your measures additive or do you have more complex definitions on your measures? 

[Dichilyn1]

The goal would be being able to build a table like this without creating a bunch of separate measure. I have measure built for the base data, but not for comparisons:

	Current Week	Last Week	L 13 Wks	MTD	QTD	YTD	Delta vs LW	Delta VS LY
Consumption	sum
Avg Inventory On hand	average
Average instock	sum/sum
Average Velocity	sum/average
Average Time Supply	sum/average