How to display Year and current YTD and last YTD sales in one chart?

[AgencyPowerBi]

Hi guys,

I want to make a chart in power bi desktop that has this data:

2014             total sales

2015             total sales

2016             total sales

YTD 2016     YTD sales

YTD 2017     YTD sales

so one solution is to make a table made out of measures (2014, 2015, YTD 2016, ytd 2017) and its values in other column, so I can then put values in chart and labels on axis.

Can you help me write a dax formula for such a table?

Or can you suggest a chart solution?

Problem is, if I stach measures in values on a chart, I get no labels on axis and it looks ugly 🙂

thanks for help!

[v-xuding-msft · Accepted solution]

Hi @AgencyPowerBi ,

By my test , the following measures you can have a try.

1. I have two tables. The table Date is a calendar  table. If you want to use time intelligence, you have to create a calendar table.  And there is a many-to-one relationship between them.

1. I created a calculated column using YEAR function in Table1.

Year = YEAR(Table1[Date])

1. Create some measures.

YTD 2017 = CALCULATE(SUM(Table1[ Sales]),DATESYTD('Date'[Date]))

YTD 2016 = CALCULATE(SUM(Table1[ Sales]),SAMEPERIODLASTYEAR('Date'[Date]),FILTER(Table1,Table1[Year] = 2016))

Sum Year = SUM(Table1[Year])

2016 =

IF(Table1[Sum Year] = 2016,

         CALCULATE (

             SUM ( Table1[ Sales]),

       FILTER (

               ALLSELECTED ( Table1 ),  

               Table1[Year] = 2016

        )

                ))

2015 =

IF(Table1[Sum Year] = 2015,

         CALCULATE (

             SUM ( Table1[ Sales]),

       FILTER (

               ALLSELECTED ( Table1 ),  

               Table1[Year] = 2015

        )

                ))

2014 =

IF(Table1[Sum Year] = 2014,

         CALCULATE (

             SUM ( Table1[ Sales]),

       FILTER (

               ALLSELECTED ( Table1 ),  

               Table1[Year] = 2014

        )

                ))

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AgencyPowerBi]

Hi,

this is all good, but how to show it in Column chart or bar chart?

Not in the table, cause this is easy and I know how to do it.

This is what I need. But it is not possible, since only one variable can be present for such a chart.

please ignore the arrow 🙂