Cumulative total for current week, current month and current Year

DAX DAX Calculations
3

Hi Fiona,

Alternatively you could expand the Date dimension table with some Offsets, that way you can easily filter your Date table by current week, month, quarter or year because they will be equal to 0.

Here’s the M code to create these offsets. Please note that for the last line of your current code you’ll need to add a comma at the end and copy its name, followed by these lines where you’ll paste the name in place of YourPreviousStepName on the first line

InsertWeekOffset = Table.AddColumn(YourPreviousStepName, "WeekOffset", each (Number.From(Date.StartOfWeek([Date]))-Number.From(Date.StartOfWeek(DateTime.LocalNow())))/7 ),
InsertMonthOffset = Table.AddColumn(InsertWeekOffset, "MonthOffset", each ((12 * Date.Year([Date])) +  Date.Month([Date])) - ((12 * Date.Year(Date.From(DateTime.FixedLocalNow()))) +  Date.Month(Date.From(DateTime.FixedLocalNow())))),
InsertQuarterOffset = Table.AddColumn(InsertMonthOffset, "QuarterOffset", each ((4 * Date.Year([Date])) +  Date.QuarterOfYear([Date])) - ((4 * Date.Year(Date.From(DateTime.FixedLocalNow()))) +  Date.QuarterOfYear(Date.From(DateTime.FixedLocalNow())))),
InsertYearOffset = Table.AddColumn(InsertQuarterOffset, "YearOffset", each Date.Year([Date]) - Date.Year(Date.From(DateTime.FixedLocalNow())))

Your cumulative measure then will look something like below:

Cumulative Sales CW =
   CALCULATE( [Total Sales],
      FILTER( ALL( Dates ),
      Dates[WeekOffset] = 0 )
   )
3 Likes