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,

 

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

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi Amelia,

     

    Have you tried the solution provided by MFelix above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

     

    If you still have any question on this issue, feel free to post here. :smileyhappy:

     

    Regards