Forum Discussion
Problem with Running total vizualisation
Hi,
I am having a problem when graphically displaying my MTD running total sales.
I use the following Dax measure that I was able to come up with after reading the http://www.daxpatterns.com/cumulative-total article.
MTD Running Total Sales =
IF (
MIN ( 'Calendar'[Date] )
<= CALCULATE (
MAX ( Sales[Date] );
FILTER ( ALL ( Sales ); RELATED ( 'Calendar'[MonthOffset] ) = 0 )
);
CALCULATE (
[Total Sales];
FILTER (
FILTER ( ALL ( 'Calendar' ); 'Calendar'[MonthOffset] = 0 );
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
)The Total Sales measure is just the explict measure for my Sales table (= SUM(Sales[Sales]))
My Calendar table provides offsets allowing me to easily create measure relative to the current date.
When displaying the result of my MTD Running Total Sales in a table, everything looks just fine, the values start and stop whenever they are supposed to:
Now, when displaying the same information using a line chart, using the Calendar[Date] on the X-Axis, everything looks just fine:
But when replacing the Dates with WeekNumbers (also coming from the Calendar table), here's what I get:
So it is basically displaying the last value calulated by the measure for weeks not part of the scope of the calculation.
I am not sure where the problem stands as the table displays things correcty and so does the line chart only when using the dates.
Any help here would be greatly appreciated.
Thanks!
14 Replies
- parry2kSuper User
I think your weeknumbers are across all the years and it is combining the data together for all the weeks from multiple years. if you add year and then week number and use drill down all level in hierarchary or create week number with year appended to it and then check.
- AnonymousNot applicable
Wah, that was quick!
Thank you so much, parry2k, adding the fiscal year did help in displaying the measure for the current fiscal year.
But my objective is to display lines for both current and previous fiscal year for comparison purposes.
Having the year added does not really allow me to do this as I would get something more like this:Which is not ideal...
- parry2kSuper User
Aha, drop week number on x axis and year on legend. that will do it.