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