Cumulative Sales Last Year DAX Formula Explanation

Explanation of the DAX Formula: Cumulative Sales LY

The DAX formula provided calculates the cumulative sales for last year up to the current date context in a selected range. Here's a breakdown of how this operates:

Formula Breakdown

Cumulative Sales LY = 
CALCULATE( 
    [Sales LY],
    FILTER( 
        ALLSELECTED( Dates ),
        Dates[Date] <= MAX( Dates[Date] ) 
    ) 
)

1. CALCULATE Function:

   • CALCULATE is used to change the context in which the data is evaluated.
   • Inside CALCULATE, you specify the expression to evaluate and the filters to apply.
   • In this case, it calculates [Sales LY] with an adjusted filter context.

2. [Sales LY]:

   • [Sales LY] is a pre-defined measure that represents the sales from the previous year.

3. FILTER Function:

   • FILTER modifies the rows of a table.
   • It filters the Dates table within the selected date context.

4. ALLSELECTED Function:

   • ALLSELECTED removes any filters that have been applied to preserves context outside of the visual level.
   • It returns all the rows within the newly defined context.

5. Logical Comparison Dates[Date] <= MAX( Dates[Date] ):

   • It operates on each row of the Dates table.
   • MAX(Dates[Date]) returns the highest date in the current context.
   • The expression Dates[Date] <= MAX(Dates[Date]) keeps all the dates up to the current date in the context.

Summary of Operations:

• The CALCULATE function is used to adjust the context of [Sales LY] so that it filters only the dates up to the current date in whatever range or context you are viewing.
• The FILTER function strips out the dates until the current maximum date in the context.
• ALLSELECTED ensures that only the intended date context is kept, regardless of other filters or selections.

Practical Example:

If you are looking at a year-to-date sales report:

• On March 31, 2023, the MAX(Dates[Date]) will be March 31, 2023.
• The FILTER function will keep all dates from the start up to March 31, 2023.
• CALCULATE then sums up [Sales LY] from the start date of the model to March 31, 2023.

For further learning, consider the courses available on Enterprise DNA Platform which cover DAX formulas, CALCULATE function, and context transition in Power BI.