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

[alicek]

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.

[amitchandak · Accepted 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]

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)

[amitchandak]

@alicek , yes this should work. With this you should be able take year as legend from date tbale

[alicek]

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!