Forum Discussion

Amelia's avatar
Amelia
Frequent Visitor
8 years ago
Solved

Stacked and Line Chart - comparing both years

Hi Guys

 

I was using the cluster colum chart to great effect to compare last year with this years orders counts.

 

 

I would now like to use the line and cluster colum chart to show the same year on year order count but also lines for year on year order values. Im using a measure to calculate cumulative order value ytd but when i add it to the chart it only shows one line for the 2016 values. this is the measure im using

 

YTDCumlativeOrderValue = TOTALYTD( SUM( Fact_Orders[OrderValue] ) , Dim_Date[DateSK])

 

and what the chart looks like.

 

 

Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    Hi Amelia,

     

    Change your formula to:

     

    YTDCumlativeOrderValue =
IF (
    ISBLANK ( SUM( Fact_Orders[OrderValue] )),
    BLANK (),
    TOTALYTD( SUM( Fact_Orders[OrderValue] ) , Dim_Date[DateSK])
)

    Should give you the expected result, another way is to have your Dim_Date to end on the current date so you won't get any date values from today onwards so on your visuals there will not be future dates.

     

    Regards,

    MFelix

6 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    Hi Amelia,

     

    How are you placing the values in the columns of your chart? Two columns one for each year?

     

    I would create this measure:

     

    PY YTDCumlativeOrderValue =
TOTALYTD (
    SUM ( Fact_Orders[OrderValue] ),
    DATEADD ( Dim_Date[DateSK], -1, YEAR )
)

    Then add it to your line also.

     

    Regards,

     

    MFelix

    • Amelia's avatar
      Amelia
      Frequent Visitor
      8 years ago

      oh MFelix thats great :-) almost what i need. Now i have to sort out the october, november & december values recurring for this year

      • MFelix's avatar
        MFelix
        Super User
        8 years ago

        HI Amelia,

         

        You want to take out those months form the lines?

         

        Regards,

        MFelix