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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
alicek
Helper II
Helper II

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.

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.

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!

alicek_0-1604025101132.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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