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 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
Yes, this is correct.