DAX Calculate context - creating own YTD

[Cymbolz]

Am testing my (lack of) knowledge with DAX.  Dataset has Sales and Calendar (date dimension table).  Existing measure to sum sales amount called [Total Sales]

Matrix with rows as Calendar[Year] and Calendar[Month].  Then adding my measures as follows:

I create the standard YTD measure and works OK.

Sales YTD = TOTALYTD([Total Sales],'Calendar'[Date])

Then borrowing some examples from various blogs, create a 'manual' version.  Also works OK and I understand how the Calendar[Date].[Year] and Calendar[Date] filter parameters - both wrapped in a MAX() function - are per the original filter context before calculate()

Sales YTD Own = CALCULATE([Total Sales],filter(all('Calendar'),'Calendar'[Date].[Year] = max('Calendar'[Date].[Year]) && 'Calendar'[Date] <= max('Calendar'[Date])))

Then I tried what I think is a slight improvement.  It works OK, but only when I have the Startofyear() parameter as values() of Calendar[Date].  If I change it just to Startofyear(Calendar[Date]) (without values()) it doesn't work, the totals add up month on month but don't reset to zero at beginning of each year. 

Why?

Sales YTD Own2 = CALCULATE([Total Sales],filter(all('Calendar'),'Calendar'[Date] >= STARTOFYEAR(values('Calendar'[Date])) && 'Calendar'[Date] <= max('Calendar'[Date])))

PS I created a measure just for Startofyear(Calendar[Date])and added to the matrix, it shows currently as 1st Jan for the year in the row of the matrix.  But obviously that's more simple than the above measure.

[v-xjiin-msft]

Hi @Cymbolz,

=> the totals add up month on month but don't reset to zero at beginning of each year. 

In your scenario, what did you mean "don't reset to zero at beginning of each year"?

Generally the use of STARTOFYEAR() shows like this:

It will return the first day of the year in the current context. So in your scenario, how did you define the calendar table?

Could you please share us some sample data which can help us repro your issue and your desired result if possible?

Thanks,
Xi Jin.

[Cymbolz]

Here's the pbix file I've created.  The data source is a few tabled from an Adventure Works mdb I found/

PBIX file

I have the total sales (monthly), then the standard YTD plus a few "alternatives" I'm experimenting with to increase my understanding of how the DAX Engine works.

The culpit is [Sales YTD Own2 wrong]

[mattbrice · Accepted solution]

Your problem is the way in which you used STARTOFYEAR.   It is a table function which triggers context transition of the row context created by the FILTER function. Your "Sales YTD Own2 wrong" code is:

FILTER (
    ALL ( 'Calendar' ),
    'Calendar'[Date] >= STARTOFYEAR ( 'Calendar'[Date] )
        && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)

and in it, STARTOFYEAR will always evaluate to first day of the year based on the currently iterated row of 'Calendar', so this part of the boolean clause will always be true for every row in 'Calendar' table (so in essense, this part of clause does nothing). Which means FILTER function only affected by the " 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) " .  MAX is a scalar function and not a table function so it does not trigger context transition so it retrieves max date in current filter context based on what you have on rows of Matrix. 

The "correct" longhand way of writing YTD formula is:  

Sales YTD =
CALCULATE (
    [Total Sales],
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[CalendarYear] = MAX ( 'Calendar'[CalendarYear] )
            && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

Make sense?

[Anonymous]

I used the following formula, But it is showing some wrong data in opening. But the wrong value is not refkecting column total.

OE YTD = CALCULATE(
[OE],
FILTER (
ALL ( 'Central' ),
Central[Year] = MAX ( Central[Year] )
&& Central[Document Date] <= MAX ( Central[Document Date] )
)
)

NS YTD = CALCULATE(
[NS],
FILTER (
ALL ( 'Central' ),
Central[Year] = MAX ( Central[Year] )
&& Central[Document Date] <= MAX ( Central[Document Date] )
)
)

OH YTD = CALCULATE(
[OE] - [NS],
FILTER (
ALL ( 'Central' ),
Central[Year] = MAX ( Central[Year] )
&& Central[Document Date] <= MAX ( Central[Document Date] )
)
)

The table showing wrong data for OE in opening.
It is not reflecting in total.
Can you please help on this?

[Cymbolz]

@Anonymous You should provide more details - the definition of measures used here, and also showing the data model would help so we see the tables / relationships.

Is there a reason you wouldn't use the YTD functions in DAX?  Is the result the same if you do?

And what is "Opening"?  That isn't a conventional month/year so may well be the cause.

[Anonymous]

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Please go throgh the pbix file bro.

[Anonymous]

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Please go through my pbix file attached

[Anonymous]

This is my MTD data.

I want to create YTD of the same data below.

I have one extra data for January. Opening value is avilable for January. One column namely Document Month Name is avilable to differentiate the opening value. Please help on this.

If I use YTD function with Document Month Name, It will show MTD data only.

If I use YTD function with Calendar , it will show YTD data.

[Anonymous]

I used this formula, It is showing some wrong data also.

OE YTD = CALCULATE(
[OE],
FILTER (
ALL ( 'Central' ),
Central[Year] = MAX ( Central[Year] )
&& Central[Document Date] <= MAX ( Central[Document Date] )
)
)

NS YTD = CALCULATE(
[NS],
FILTER (
ALL ( 'Central' ),
Central[Year] = MAX ( Central[Year] )
&& Central[Document Date] <= MAX ( Central[Document Date] )
)
)

OH YTD = CALCULATE(
[OE] - [NS],
FILTER (
ALL ( 'Central' ),
Central[Year] = MAX ( Central[Year] )
&& Central[Document Date] <= MAX ( Central[Document Date] )
)
)

The table showing wrong data for OE in opening.
It is not reflecting in total.
Can you please help on this?

[Cymbolz]

Thank you @mattbrice for the explanation.

I get it, sort of, I think.  Am still coming to grips with the concept of context.  Is there any decent source that indicates which context DAX functions operate over/in?

And to paraphrase your explanation and putting a bit of an example to it for the "wrong" measure...

• There is a query context coming from the matrix rows (no columns, no other slicers/filters)
• CALCULATE will, based on that query context, modify or add to the filter context...firstly
• FILTER creates a row context, for ALL rows of Calendar where (row by row) the date is >= the value from STARTOFYEAR
• But STARTOFYEAR creates a filter (?) context from the previous row context, this new filter context is for "all" of the Calendar table.  Thus STARTOFYEAR returns the earliest value in this context ie earliest date in the Calendar[Date] column
• The MAX is refering to the Calendar[Date] of the current filter context, which is not impacted by FILTER itself, so is the latest date based on the query context
• FILTER returns a table of dates from earliest in the Calendar table to latest of the current month of the matrix.  
• CALCULATE uses this table to perform a filter context change on the Calendar[Dates].  This follows the relationship to Sales.  Then the measure [Total Sales] is computed

Right?

DAX desperately needs a debugger to look inside the goings-on !

[mattbrice]

If you want end up in the deep end of the pool, this is the book to read:  Definitive Guide to Dax.  Written by the guys over at sqlbi.com:  Marco Russo, Alberto Ferrari.  Their blog post are also must reads.  https://www.sqlbi.com/

And to respond to your bullet points:

• Initial filter conext, yes
• I often refer to this by one of the Microsoft Developers Jeffrey Wang:

  • Calculate function performs the following operations:

    1. Create a new filter context by cloning the existing one.
    2. Move current rows in the row context to the new filter context one by one and apply blocking semantics against all previous tables.
    3. Evaluate each setfilter argument in the old filter context and then addsetfilter tables to the new filter context one by one and apply blocking semantics against all tables that exist in the new filter context before the first setfilter table is added.
    4. Evaluate the first argument in the newly constructed filter context
• FILTER does create a row context for ALL rows of Calendar.  Its second parameter evaluates the row context in its boolean expression
• STARTOFYEAR does not create a filter context; FILTER does.  STARTOFYEAR is a table function so it transitions the currently iterated row of row context created by FILTER (in previous step) into the filter context.  If Calendar[Date] column already had a filter on it from row/column/slicer of your matrix, this step would replace that column filter with this currently iterated row context value.   But in this case, there is no blocking/replacing.  So Calendar[Date] has as a filter the currently iterated row of row context, which causes Calendar[Date] in function call to be the same value.  Then the STARTOFYEAR function finds the first day of the year based on this number which is why ALL values in Calendar will be >= so will return all values.
• Yes.  
• Yes
• Yes.  CALCULATE takes the newly constructed filter context and applies it to the model as per step #4 above. 

  "DAX desperately needs a debugger to look inside the goings-on !"

Amen to that.

[Cymbolz]

Thank you @mattbrice, I definitely am heading to the deep end and appreciate the pointer to the book.

Two questions come to mind that may be quick to answer.

1 - you said " If Calendar[Date] column already had a filter on it from row/column/slicer of your matrix, this step would replace that column filter with this currently iterated row context value.   But in this case, there is no blocking/replacing.  So Calendar[Date] has as a filter the currently iterated row of row context, which causes Calendar[Date] in function call to be the same value"

But Calendar[Date] is on the matrix row.  But I think the outcome is the same either way, the filter on Calendar[Date] (from the Query context) is replaced with the row of row context within FILTER.  Right?

2 - I've seen the term 'blocking' used often in blogs/articles but no clear description.  Does it mean, when there is an existing filter on a column, "blocking" is where that column filter is maintained as-is...unless the new context "replaces" that column filter (either defining a new filter condition on that column, or a variation of ALL).  How right or wrong am I?   

My terminology might be wrong too, my previous post was largely along the lines of what you responded with (I feel) but the way I described it may be determined as being incorrect!   Thanks for articulating correctly in your reply.

[mattbrice]

First, I need to make a correction on one of my bullet points (fourth one).  I meant to write:

"STARTOFYEAR does not create a Row context; FILTER does."

And another point worth mentioning is that there is always only one Filter Context and only one Row Context.  I think of them as two boxes with values filtering a particular column that change when moving from once "cell" to another in Matrix.  Per the steps by Jeffrey Wang, the values in either box can be modified or removed/blocked/overwritten with functions like CALCUALTE.

To reply to your 2 questions:

1.  I thought you had put Calendar[CalendarYear] and Calendar[Month] only on rows of Matrix - not Calendar[Date](?).   Keep in mind that filters are column specific.  If you do have Calendar[Date] on rows of matrix then it is in the initial, cloned filter context.  But then the filtering value is replaced by the row context transition in your example. 

2. You are mostly correct on this one.  If your function call was:  CALCUALTE ( SUM ( Fact[Amount] ), ALL ( Calendar[Month] ) ) then the existing filters on the column Calendar[Month] are blocked ( or removed ) from Filter context box prior to Filter context being applied to the model.  From what i have read "blocked" is older terminology and now "overwrite" is newer(?)

 I am far from an expert on all of this, but this is all my understanding.  Thanks.

[Cymbolz]

1 - The matrix has Calendar[Date].[Year] and Calendar[Date].[Month] on rows.  This utilises the automatic date hierarchy PBI created on the Calendar[Date] field.  It's my understanding any filter on Calendar[Date] will apply to it's children, and vise versa, right?  And would the same apply to any manually created hierarchies (I doubt it would)?

And a question on the topic of one filter context.  That bit is simple to understand in isolation.

Is a filter context scoped to that part of the measure only?  So only one exists at any point in time (any evaluation moment), but eg a filter context only exists within a calculate() expression so once that has returned a result the previous filter context is 're-instated' or preserved?

such as

= calculate() + calculate()

or

= some_function( calculate(). calculate(), calculatetable() )

Very hypothetical, my very limited testing says the context only existing within the scope and doesn't carry-on or impact outside / after that scope.

[mattbrice]

Can you post a screenshot of actual output that is incorrect?

[Cymbolz]

Below screenshot, the Sales YTD Own is per my original post.

I removed the values() part of the measure for Sales YTD Own2 so it is:

Sales YTD Own2 = CALCULATE([Total Sales],filter(all('Calendar'),'Calendar'[Date] >= STARTOFYEAR('Calendar'[Date]) && 'Calendar'[Date] <= max('Calendar'[Date])))

Note when year value changes, Sales YTD Own resets while the second version doesn't unless I include the values() part.