Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
This solution works excellent, How i can sort the months chronologically? Jan, Feb, Mar, Apr, .........YTD
tks.
Thank you for the solution. It worked for me.
I don't get your question.
Put the "YEAR" in the Legend dimension
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
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.
I am able to do it in excel, need solution to implement same in Power BI
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
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |