Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello all,
We are trying to create a clustered bar chart. The y-axis is the total number of cases, the x-axis is each of our building locations, and the legend represents the year. Currently, we only have data rom all of 2019 and to-date from 2020.
I cannot figure out how to create a measure that we would automatically only show data YTD for 2020 and YTD for 2019 (ignoring any data we have for days in 2019 that have not already passed in 2020).
Does this make sense?
Here's some fake data (our actual data would have multiple rows per day, each one represnting a case that day... so a coumn for Case ID, and a column for Date, and a column for Location.)
Month-Year | Total Cases | Location |
Jan 2019 | 20 | A |
Feb 2019 | 20 | A |
March 2019 | 20 | A |
April 2019 | 20 | A |
May 2019 | 20 | A |
June 2019 | 20 | A |
July 2019 | 20 | A |
August 2019 | 20 | A |
September 2019 | 20 | A |
October 2019 | 20 | A |
November 2019 | 20 | A |
December 2019 | 20 | A |
January 2020 | 10 | A |
February 2020 | 10 | A |
Jan 2019 | 30 | B |
Feb 2019 | 30 | B |
March 2019 | 30 | B |
April 2019 | 30 | B |
May 2019 | 30 | B |
June 2019 | 30 | B |
July 2019 | 30 | B |
August 2019 | 30 | B |
September 2019 | 30 | B |
October 2019 | 30 | B |
November 2019 | 30 | B |
December 2019 | 30 | B |
January 2020 | 5 | B |
February 2020 | 5 | B |
The clustered bar chart should look like this:
(Do you see how it's summing only Jan and Feb for 2019, even though the underlying data includes March - Dec 2019? Otherwise, the 2019 bars would each equal cases in the hundreds instead. I did this manually in Excel by creating a pivot chart from the underlying data, then adding Month-Year as a filter and unselecting those months in the filter. I could manually do this maybe in PBI, but hoping for a measure to do it automatically).
I tried to create a meassure using the TOTALYTD and TOTALMTD, but neither worked -- I thought TOTALMTD would, but it didn't just take months, it actually was taking month-year.
I know how to do this when the x-axis itself is the date field, but not when the x-axis is a categorical variable and the legend is the data variable. All thoughts appreciated!! Thanks in advacne! Let me know if you need any more clarification.
Solved! Go to Solution.
@alicek , Try a YTD measure like one the two using date tbale
YTD QTY forced 2=
var _max = MONTH(today())
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),MONTH('Date'[Date])<=_max)
YTD QTY forced 2=
var _max = MONTH(maxx(allselected(Date),Date[Date]))
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),MONTH('Date'[Date])<=_max)
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
@alicek , Try a YTD measure like one the two using date tbale
YTD QTY forced 2=
var _max = MONTH(today())
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),MONTH('Date'[Date])<=_max)
YTD QTY forced 2=
var _max = MONTH(maxx(allselected(Date),Date[Date]))
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),MONTH('Date'[Date])<=_max)
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Thanks @amitchandak ! I actually do use a date table and there's a one to many relationship from the date table to the date column in my data table.
For your measures below -- since unlike in my easy example above in the question, the underlying data actually is each row is a unique CASE ID, instead of SUM('table'[QTY]), could I instead use DISTINCTCOUNT'table'[CaseID]?
For example --
YTD_QTY_1 =
var _max = MONTH(today())
return
calculate(COUNTDISTINCT('CaseID'),DATESYTD('Date'[Date]),MONTH('Date'[Date])<=_max)
YTD_QTY_2 =
var _max = MONTH(MAXX(allselected(Date),Date[Date]))
return
calculate(COUNTDISTINCT('CaseID'),DATESYTD('Date'[Date]),MONTH('Date'[Date])<=_max)
@alicek , yes this should work. With this you should be able take year as legend from date tbale
Thank you so much @amitchandak ! The first measure you offered worked! I marked you as the solution. Just a note -- your second one did't work, as PBI gave a yellow error bar saying the syntax was wrong. But the first one worked, so we're good to go, can't thank you enough!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
118 | |
79 | |
47 | |
45 | |
32 |
User | Count |
---|---|
172 | |
90 | |
66 | |
46 | |
45 |