Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Dichilyn1
Frequent Visitor

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

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. 

Dichilyn1_0-1690408460918.png

 

8 REPLIES 8
julioverdugo
Frequent Visitor

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_0-1690414522234.png

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

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

           )

        )

    )

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

 

 

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?

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). 

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
Frequent Visitor

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 WeekLast WeekL 13 WksMTDQTDYTDDelta vs LWDelta VS LY
Consumptionsum       
Avg Inventory On handaverage       
Average instocksum/sum       
Average Velocitysum/average       
Average Time Supplysum/average       

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors