Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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/cu...
Need Help
parry2k's avatar
parry2k
Super User
8 years ago

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

 

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

 

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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.