Problem with Running total vizualisation

[jlouvel]

 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!

[parry2k]

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.

[jlouvel]

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...

[parry2k]

Aha, drop week number on x axis and year on legend. that will do it.

[jlouvel]

For some reason, I cannot drop Year on legend, it would not le me do it...

[parry2k]

do you have more than one field in values? you have to have one field in values, can you send screen shot what kind of field you have on axis, value, legend?

[jlouvel]

I have 2 values (the 2 measures I want to compare)

- MTD Running Total Sales

- Prev MTD Running Total Sales

Here's a screenshot:

[jlouvel]

I do something very similar in another visualization using another set of measures and a Area Chart and it works just fine, without having to add the year as a legend.

So I tend to think there is something wrong with my Running Total measures...

[parry2k]

can you try to remove one value and atleast test it by using year in legend and then we can talk about what else is going on.

there is no way that you can have two values and legend that is for sure. based on your original post, you just gave the example with one value and all the discussion was around that

[jlouvel]

You are right, sorry, let's go step by step.

Having only one value and the year in legend is working as expected.

[parry2k]

ok, atleast we are on same page, now what next? 🙂

[jlouvel]

So what I am trying to accomplish here is to have a chart line displaying the previous and current fiscal year running total sales over the same period of time.

And to do so, I have the 2 following measures (note that in my original post, the measures where MTD and prev MTD, but as we just switched to a new fiscal month, I have to use the YTD version to get more data to display)

YTD Running Total Sales = 
IF (
    MIN ( 'Calendar'[Date] )
        <= CALCULATE (
            MAX ( Sales[Date] );
            FILTER ( ALL ( Sales ); RELATED ( 'Calendar'[YearOffset] ) = 0 )
        );
    CALCULATE (
        [Total Sales];
        FILTER (
            FILTER ( ALL ( 'Calendar' ); 'Calendar'[YearOffset] = 0 );
            'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        )
    )
)

Prev YTD Running Total Sales = 
VAR CurrentDayNumber =
    MAX ( YTDInfos[CurrentDayNumber] )
RETURN
    IF (
        MIN ( 'Calendar'[Date] )
            <= CALCULATE (
                MAX ( Sales[Date] );
                FILTER (
                    ALL ( Sales );
                    AND (
                        RELATED ( 'Calendar'[YearOffset] ) = -1;
                        RELATED ( 'Calendar'[DayNumber] ) <= CurrentDayNumber
                    )
                )
            );
        CALCULATE (
            [Total Sales];
            FILTER (
                FILTER (
                    ALL ( 'Calendar' );
                    AND ( 'Calendar'[YearOffset] = -1; 'Calendar'[DayNumber] <= CurrentDayNumber )
                );
                'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
            )
        )
    )

When displaying those 2 measures in the same Line Chart using the week number on the X-Axis, here is what I get:

When what I would like to get is more something like this:

Meaning the weeks on the right side of the red line to be removed and the previous year to be displayed correctly (blue dotted line, instead of the black constant line).

I don't understand why the Chart line is displaying things like this when the same data displayed in a table looks perfect.

[parry2k]

here is the thing, you cannot compare two values and also have legend. either you have to remove one value and use legend.

[jlouvel]

I have 2 values:

[parry2k]

you cannot have two values if you want to use legend, remove one and then try with year in legend, also in that case you can remove year from x axis.