DAX running sum in custom periods Year to year

Question

I have a table with the name of certain promotional periods and their start and end dates for the current year (every year it will be a new table with slightly different periods). I need to calculate running sum of sales between the start and end date of selected period and compare it to the same period last year and show it on a chart for comparison.

What does the period/date table look like? The periods are different each year, how do you compare them? By name, or number? Could you post a screenshot of the tables, with columns? — TJ_

Uzzy Uzzy · Accepted Answer · 2018-03-22T19:37:43

Ok, so I've found a workaround, but it's working: First I take just day and month from start and end dates for each period:

Start = FORMAT(VALUES('Promo'[start]); "dd-MM")
End = FORMAT(VALUES('Promo'[end]); "dd-MM")

then for the current year and for previous year I create measures used for filtering the sales:

Sales start = DATEVALUE([Start] & "-" & [Year])
Sales start LY = DATEVALUE([Start] & "-" & [Y-1])
Sales end = DATEVALUE([End] & "-" & [Year])
Sales end LY = DATEVALUE([End] & "-" & [Y-1])

and I use those dates in the running total calculation for this year and Y-1.

Now when I make a slicer with Promotional period names and select one of the periods all is being calculated correctly.

The thing I still need to solve is how to put those running totals on one chart for comparison.

DAX running sum in custom periods Year to year

1 Answers