Need help creating a visual

[Bareak]

I am creating a column chart visual comparing the the past 5 years sales.  I created measures to calculate the YTD shipments for the past 5 years.  I am filtering on the current year (2021) to display the data.

Now I am trying to add these values to a visual, but I am needing them to be displayed as the year (x-axis) and the shipment amount(y-axis). I created the clustered bar chart and drug all the values over, but this is not visual I am looking for.  I am needing all the values to be in a single field so that I can see each year on the x-axis.  I am thinking I am need to somehow do a union on the values to put them in their own table so that the values are a single column instead of 6, but I'm not sure how to do that.  

Any help would be appreciated.

[Bareak · Accepted solution]

I was able to solve this by using the video provided by Avi Singh: https://www.youtube.com/watch?v=WBddNp_25YY.  I modified the DAX formulas to work with my fiscal date calendar, then applied a filter on the visual to show the top N records.

Our data is batch loaded overnight, so I am using the current day minus 1 to determine the most recent fiscal day.

Max Fiscal Day = CALCULATE(MAX('Date Table'[FiscalDay]), ALL('Date Table'), 'Date Table'[Actual_Date_DT] = TODAY() - 1)

I modified the DAX in the video to:

Shipment YoY = VAR MaxDate = [Max Fiscal Day]

RETURN CALCULATE([Shipments],'Date Table'[FiscalDay] <= MaxDate)

This gives me my most recent Fiscal Day.  I can then drop in the visual and filter it on the top 6 records, sorted by fiscal year.

[m3tr01d]

@Bareak Ok, I would try something like this first just to see if you are able to have the same results :

[YTD Shipments] =
--Get the last Shipment date
VAR _Latest_Shipment Date = CALCULATE(MAX(Shipments[Invoice Date]), ALL(Shipments))

--Use the last shipment date to calculate the YTD variation
RETURN
CALCULATE(
	[Total Shipments],
	DATESYTD(
		'DIM Date'[Cal Date]
	),
	'DIM Date'[Cal Date] = _Latest_Shipment,
	ALL( 'DIM Date'[Cal Date] )
)

[YTD Shipments 1YP] = 
VAR _Latest_Shipment Date = CALCULATE(MAX(Shipments[Invoice Date]), ALL(Shipments))

--Use the last shipment date to calculate the YTD variation for the previous year
RETURN
CALCULATE(
	[YTD Shipments],
	DATEADD(
		'DIM Date'[Cal Date],
		-1,
		YEAR
	)	
)

You will need to change some Column names and Table names based on your data.

[Bareak]

I created the two measures above, appending Test to the measure name.

YTD Shipments Test = 
--Get the last Shipment date
VAR _Latest_ShipmentDate = CALCULATE(MAX(Shipments[Invoice Date]), ALL(Shipments))

--Use the last shipment date to calculate the YTD variation
RETURN
CALCULATE(
	[Total Shipments],
	DATESYTD(
		'Date Table'[Actual_Date_DT]
	),
	'Date Table'[Actual_Date_DT] = _Latest_ShipmentDate,
	ALL( 'Date Table'[Actual_Date_DT] )
)


YTD Shipments 1YP Test = 
VAR _Latest_Shipment_Date = CALCULATE(MAX(Shipments[Invoice Date]), ALL(Shipments))

--Use the last shipment date to calculate the YTD variation for the previous year
RETURN
CALCULATE(
	[YTD Shipments Test],
	DATEADD(
		'Date Table'[Actual_Date_DT],
		-1,
		YEAR
	)	
)

Here are the values returned compared to the current measures, previous measures on the left.

[m3tr01d]

Ok, 
1) Can you tell me the code for measure Total Shipments?
2) Do you have any other filters on the Report or on the Card visuals?

[Bareak]

Thank you for your help.

1)  

Total Shipments = round(sum('Shipments'[Ext Price]),2)

YTD Shipments = TOTALYTD([Total Shipments], 'Date Table'[Actual_Date_DT])

2)  The only report filter is at the page level and it is on the year (locked to 2021).

[m3tr01d]

Hello @Bareak,

let's say you want to have these values on the X axis : 1YP, 2YP, 3YP, 4YP, 5YP

You could have a disconnected table with YearValues that would contain only one column with these values : 
1YP, 2YP, 3YP, 4YP, 5YP.

Then you can build a single measure that will use the value and adjust the calculation. It would help us if you provide us the DAX between your measures.

[Bareak]

Thanks for the quick reply.  Here is the formula that I use to determine previous years YTD shipments.  For additional previous years, I change the 2 in the dateadd formula to 3, 4, 5.  Latest shipment date is a measure that finds the most recent date we have shipments.

YTD Shipments 2YP = calculate([Total Shipments], dateadd( INTERSECT (

VALUES ('Date Table'[Actual_Date_DT]),

DATESBETWEEN ('Date Table'[Actual_Date_DT], BLANK (), [Latest Shipment Date])

),-2,year))

Latest Shipment Date = CALCULATE(MAX(Shipments[Invoice Date]), ALL(Shipments))

[m3tr01d]

Ok so first, I would like to see if we can get this measure cleaner. 
If you have selected the year 2021 in your report. I assume :

YTD 1YP would be from Jan 1st 2020 to July 26th 2020?
YTD 2YP would be from Jan 1st 2019 to July 26th 2019?

Can you confirm if it's correct or give the correct date range for these examples

[Bareak]

Yes, this is correct.