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

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

    • Amelia's avatar
      Amelia
      Frequent Visitor
      8 years ago

      MFelix yes, i belive i can add a filter to TOTALYTD but having trouble with the syntax. the ye;llow line is the current year cumulative value based on

       

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

       

      but obviously only want it to go to the current month - september

       

       

      still getting up to speed on DAX

       

      • MFelix's avatar
        MFelix
        Super User
        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