DAX Problem with Running Total

[losamfr17]

Hello dear wizards,

I need a measure to calculate running total for the week, month and season. My sample table and preferred output are visible below.
I searched the web and tried "=CALCULATE(SUM([Sales]), FILTER( ALL(Source[WeekNumber]), [Week Number] <= MAX(Source[Week Number]) ) )", but the running total did not work for the month and season subtotals.

Could you please advise?

FYI: in my data model, I do not have a date column.

ChainName	WeekNumber	MonthName	SeasonName	Sales
Belk	12​	April	SPRING	9.5​
Sephora	11​	April	SPRING	4.9​
Sephora	15​	May	SPRING	3.7​
Ulta	10​	April	SPRING	357​
Ulta	12​	April	SPRING	310​
Ulta	8​	March	SPRING	340​
Ulta	13​	April	SPRING	319​
Ulta	48​	December	FALL	434​
Ulta	48​	December	FALL	336​
Ulta	9​	March	SPRING	331​
Nordstrom	4​	February	SPRING	0.1​
Sephora	16​	May	SPRING	4.3​
Nordstrom	7​	March	SPRING	0​
Sephora	10​	April	SPRING	3.6​

 

[citizenbh]

In an ordinary Pivot Table:

Make sure the numbers are numbers?

 

[pjmorris]

Consider restructuring your data - strictly the MonthName column adds no more detail than the week number. I'd prefer to have actual dates against each Chain Name. Then create a Calendar Table in PowerPivot (it can do it for you, though you'd need to add the Season as another column). Then link the Dates column in Calendar and your data table. You would probably use the Time Intelligence functions to provide your running totals.

Another way of doing so is to have the calendar as a disconnected table, define measures of [StDate]=min(Calendar[Date]) and [EndDate]=max(Calendar[Date]) and then use those two measures as filter parameters in measures to arrive at your running totals.

Hope that points in the right direction.

 

[losamfr17]

In an ordinary Pivot Table:
View attachment 15846

Make sure the numbers are numbers?

Numbers are set as numbers. MonthName as text. In your screenshot you can see that the running total does not follow through the Month subtotals and the season subtotals. For example, May is less than April and Fall is less than Spring. In a running total each week would be greater than the earlier week.

 

[losamfr17]

Consider restructuring your data - strictly the MonthName column adds no more detail than the week number. I'd prefer to have actual dates against each Chain Name. Then create a Calendar Table in PowerPivot (it can do it for you, though you'd need to add the Season as another column). Then link the Dates column in Calendar and your data table. You would probably use the Time Intelligence functions to provide your running totals.

Another way of doing so is to have the calendar as a disconnected table, define measures of [StDate]=min(Calendar[Date]) and [EndDate]=max(Calendar[Date]) and then use those two measures as filter parameters in measures to arrive at your running totals.

Hope that points in the right direction.

Thank you for that. I might try that since nothing has worked so far.

 

[pjmorris]

one thing to remember is that Excel (like all computers is pretty daft), it doesnt understand what 'April' means either in the context of week numbers or that it comes before 'May'.

I think you should get some useful results by restructuring - good luck.