Use the Period To Date Cumulative Total pattern to change the context from the particular selected date to all dates up to and including the current date in the current period.
For example, to calculate the cumulative sales for the month to date, quarter to date, and year to date, the following could be used:
Sales MTD =
// DAX PATTERN NAME: Period to Date (Month) Cumulative Total
// NOTES: Use CALCULATE to change the context from the particular selected date to all dates up to and including the current date
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
CALCULATE( [Total Sales],
DATESMTD( Dates[Date] ) )
Sales QTD =
// DAX PATTERN NAME: Period to Date (Quarter) Cumulative Total
// NOTES: Use CALCULATE to change the context from the particular selected date to all dates this quarter up to and including the current date
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
CALCULATE( [Total Sales],
DATESQTD( Dates[Date] ) )
Sales YTD =
// DAX PATTERN NAME: Period to Date (Year) Cumulative Total
// NOTES: Use CALCULATE to change the context from the particular selected date to all dates this year up to and including the current date
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
CALCULATE( [Total Sales],
DATESYTD( Dates[Date] ) )
Alternatively, one could use the TOTALMTD, TOTALQTD, and TOTALYTD functions:
Sales MTD (Alternate) =
TOTALMTD( [Total Sales], Dates[Date] )
Sales QTD (Alternate) =
TOTALQTD( [Total Sales], Dates[Date] )
Sales YTD (Alternate) =
TOTALYTD( [Total Sales], Dates[Date] )
Tips:
- To replace all occurrences of a text string = select one occurrence, use CRTL + SHIFT + L, type the replacement text
- To zoom the text size = use CRTL + mouse scroll wheel
