 # Cumulative total for current week, current month and current Year

Hello,
I need 3 measures that will give me the cumulative total for the current week, current month and current year.

I’ve got the standard cumulative total calculation to start off with :
Cumulative Sales =
CALCULATE( [Total Sales];
FILTER( ALL( Dates[Date] );
Dates[Date] <= MAX( Dates[Date] ) ))

My other key measure is Today’s date : Today Date = TODAY()

In my graph, I will only have the product name. For a product, I need to know the cumulative total for the current week, month and year in regards to today’s date.

This is where I am stuck. How can I integrate my measure “Today Date” into the standard cumulative total to calculate the Total Sales for each measure - ie current week, current month, current year?

I’m using the Date dimension provided in the course,

Fiona

If you still have questions afterward, just give a shout.

• Brian
1 Like

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

Hi @Fiona, A response to this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

Thanks heaps @Melissa I will test and confirm