Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have data for 2 years 2017 (jan-oct) and 2016 (jan-dec) - columns year, month and a sum and i want to vizualize a graph and a month filter with data ytd also for 2016.
I think i need to make a measure with max month of max year but i don't know how. After that,how can be input in filter?
I'm new with Power BI, maybe it's very easy but i don't know how to manage; it's not like in qlikview i suppose
can anyone help me please with this issue?
Thanks in advance,
Cosmin
Solved! Go to Solution.
Hi @cosminc,
If I understand you correctly, you can firstly add a Year-Month column in your table.
Year-Month = 'Table1'[year] * 100 + 'Table1'[month]
And use the formula below to create a measure to calculate the YTD sum.
YTD = CALCULATE ( SUM ( 'Table1'[sum] ), FILTER ( ALL ( 'Table1' ), 'Table1'[year] = MAX ( 'Table1'[year] ) && 'Table1'[month] <= MAX ( 'Table1'[month] ) ) )
Note: You'll need to replace 'Table1' with your real table name.
Then you should be able to show Year-Month column as Axis, the measure [YTD] as Values on a chart visual. And use Year-Month column or just year column as Slicer on your report.
Regards
Hi @cosminc,
If I understand you correctly, you can firstly add a Year-Month column in your table.
Year-Month = 'Table1'[year] * 100 + 'Table1'[month]
And use the formula below to create a measure to calculate the YTD sum.
YTD = CALCULATE ( SUM ( 'Table1'[sum] ), FILTER ( ALL ( 'Table1' ), 'Table1'[year] = MAX ( 'Table1'[year] ) && 'Table1'[month] <= MAX ( 'Table1'[month] ) ) )
Note: You'll need to replace 'Table1' with your real table name.
Then you should be able to show Year-Month column as Axis, the measure [YTD] as Values on a chart visual. And use Year-Month column or just year column as Slicer on your report.
Regards
Most articles I've run into for YTD for some reason I have to apply a visual filter for the current year. This is the first article where I do not have to apply a filter to a visual based on YTD, because its based on max year in the calendar table or in this case the "Dates" table.
The only additional change that I would add to this, is to use the Month Number for Max Month. The Max function needs a numeric value, doesn't work with text like month name.
Use the following in the forumula:
&& Dates[Month Number]<= MAX ( Dates[Month Number])
and it will be an integer comparison, then it works great.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |