Forum Discussion

Bareak's avatar
Bareak
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's avatar
    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.
     

     

     

     

m3tr01d's avatar
m3tr01d
Icon for Continued Contributor rankContinued Contributor
5 years ago

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's avatar
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.

 

 

  • m3tr01d's avatar
    m3tr01d
    Icon for Continued Contributor rankContinued Contributor
    5 years ago

    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's avatar
      Bareak
      New Member
      5 years ago

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