Forum Discussion

New Member

5 years ago

Solved

Need help creating a visual

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

Need Help

Bareak
5 years ago
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.

Bareak

New Member

5 years ago

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.