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

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?

16 Replies

  • mattbrice's avatar
    mattbrice
    Icon for Solution Sage rankSolution Sage
    8 years ago

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

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

      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.

       

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Icon for Solution Sage rankSolution Sage
    8 years ago

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

      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's avatar
        mattbrice
        Icon for Solution Sage rankSolution Sage
        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?