Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
alicek
Helper III
Helper III

Year-to-Date with Year as Legend and a Categorical Variable as the X-Axis variable (not a date)?

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-YearTotal CasesLocation
Jan 201920A
Feb 201920A
March 201920A
April 201920A
May 201920A
June 201920A
July 201920A
August 201920A
September 201920A
October 201920A
November 201920A
December 201920A
January 2020 10A
February 202010A
Jan 201930B
Feb 201930B
March 201930B
April 201930B
May 201930B
June 201930B
July 201930B
August 201930B
September 201930B
October 201930B
November 201930B
December 201930B
January 2020 5B
February 20205B


The clustered bar chart should look like this:

 

alicek_0-1604021344396.png

 

 

(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.

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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!

alicek_0-1604025101132.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric Community.