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

Question

Hi All,&nbsp;&nbsp;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.&nbsp;&nbsp;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))&nbsp;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:&nbsp;Below is the mess I have going on now in tabular editor.&nbsp;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.&nbsp;&nbsp;Please save me.&nbsp;&nbsp;

dichilyn1 · Answer

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:&nbsp;&nbsp;Current WeekLast WeekL 13 WksMTDQTDYTDDelta vs LWDelta VS LYConsumptionsum&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Avg Inventory On handaverage&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Average instocksum/sum&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Average Velocitysum/average&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Average Time Supplysum/average&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

julioverdugo · Answer

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 · Answer

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

julioverdugo · Answer

Try this:&nbsp;VAR CurrWMWK =&nbsp; &nbsp; SELECTEDVALUE( 'Calendar'[WM Chronological Order])RETURN&nbsp; &nbsp;IF(&nbsp; &nbsp; &nbsp; &nbsp; ISSELECTEDMEASURE([OH Qty], [OH Cost], [POS Qty]),&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DIVIDE( SELECTEDMEASURE(), COUNTROWS('Calendar')), --or the calendar table you need&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ALL('Calendar'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Calendar'[WM Chronological Order] &lt;= CurrWMWK - 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&amp;&amp; 'Calendar'[WM Chronological Order] &gt; CurrWMWK - 14&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )

dichilyn1 · Answer

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

Forum Discussion

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

8 Replies

Related content

MTD,YTD Calculation on Non-Standard Date tables

Last Year Comparisons for Non-Standard Calendars

SUM function incorrectly calculates weekly sales for Non Standard Date calendar

DAX dynamic measure, non standard calendar

Column non standard data format

	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