Cumulative total for current week, current month and current Year

DAX DAX Calculations
1

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,

Thanks heaps in advance
Fiona

2

@Fiona,

Here are two excellent videos that should help address your question:

https://forum.enterprisedna.co/t/dynamically-compare-monthly-information-cumulatively-in-power-bi-wdax/508

https://forum.enterprisedna.co/t/cumulative-totals-only-up-to-specific-dates-dax-tutorial-for-power-bi/2987

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

  • Brian
1 Like
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
4

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!

5

Thanks heaps @Melissa I will test and confirm

6

Thanks BrianJ for your reply, I’ll review video and test

7

Hi Melissa,
Awesome solution thanks heaps. I tested and it works perfectly