Forum Discussion

Cymbolz's avatar
Cymbolz
Icon for Helper III rankHelper III
8 years ago
Solved

DAX Calculate context - creating own YTD

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] a...
Need Help
  • mattbrice's avatar
    mattbrice
    8 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Cymbolz
Icon for Helper III rankHelper III
7 years ago

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.