Display Month-wise and YTD data in one visual

[Anonymous]

Hi Guys,

I am trying to display month-wise and  YTD data in one visual, which I am not able to do it now. Is there any provision to do this in Power BI?

I have attached a sample snapshot with this, that I want to achieve in power BI. Please suggest any visual if any or DAX.

Sample graph

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Please check the following steps as below.

1. Create a calculated table and relat it to the fact table like this.

YM = UNION(VALUES('Table'[Year]),VALUES('Table'[Month]))

2. Then we can create a measure to get the excepted result as we need.

Measure = var a = SUM('Table'[value])
return
IF(ISBLANK(a),CALCULATE(SUM('Table'[value]),USERELATIONSHIP('Table'[Year],YM[Year])),a)

For more details, please check the pbix as attached.

[Caballero]

This solution works excellent, How i can sort the months chronologically? Jan, Feb, Mar, Apr, .........YTD

tks.

[Anonymous]

Thank you for the solution. It worked for me.

[Minseven]

I don't get your question.

Put the "YEAR" in the Legend dimension

[RobbeVL]

Hi,

You'll be able to use this DAX measure:
SAMEPERIODLASTYEAR()
https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax 

just do ex.
CALCULATE(SUM(SALES); SAMEPERIODLASTYEAR(DATE))

And you should have a measure with the data from last year.

Robbe

[Anonymous]

Thanks

We have already used that DAX, but it is not working.

What I want is one bar for past year and for this year(i.e 2019), the graph should show month-wise, this both(past year and 2019 month-wise) in one graph as shown in the screenshot.

[Anonymous]

I am able to do it in excel, need solution to implement same in Power BI

[RobbeVL]

Hi,

DataViz wise, you really created a monster here.

Any specific reason why you would do it like this?

For doing this you should create a custom table that would precalculate all values you need. 

robbe