Forum Discussion
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...
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.
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?
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).