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

Hello Bareak,

let's say you want to have these values on the X axis : 1YP, 2YP, 3YP, 4YP, 5YP

You could have a disconnected table with YearValues that would contain only one column with these values : 
1YP, 2YP, 3YP, 4YP, 5YP.

Then you can build a single measure that will use the value and adjust the calculation. It would help us if you provide us the DAX between your measures.


  • Bareak's avatar
    Bareak
    New Member
    5 years ago

    Thanks for the quick reply.  Here is the formula that I use to determine previous years YTD shipments.  For additional previous years, I change the 2 in the dateadd formula to 3, 4, 5.  Latest shipment date is a measure that finds the most recent date we have shipments.

     

    YTD Shipments 2YP = calculate([Total Shipments], dateadd( INTERSECT (
    VALUES ('Date Table'[Actual_Date_DT]),
    DATESBETWEEN ('Date Table'[Actual_Date_DT], BLANK (), [Latest Shipment Date])
    ),-2,year))
     
    Latest Shipment Date = CALCULATE(MAX(Shipments[Invoice Date]), ALL(Shipments))
    • m3tr01d's avatar
      m3tr01d
      Continued Contributor
      5 years ago

      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

      • Bareak's avatar
        Bareak
        New Member
        5 years ago

        Yes, this is correct.