Forum Discussion

Rich_Wyeth's avatar
Rich_Wyeth
Icon for Helper I rankHelper I
1 year ago
Solved

Last Order Date and Age

Hi all,

 

I have a table of data that runs for each day from 2022 to 2024. Within this table, there could be multiple lines for Company Names, which each have a date attached. I can create a table that shows the company name and the date and select from the date to show the latest one. This works fine.

 

I then want to calculate the number of days from today based on that latest date, which I did as a measure, but it doesn't work, it either fails the graphic or re-apples all dates with the number of days alongside.

 

Ultimately I want the table to show only the last time someone ordered, then to show how many days this was, so that I can filter out anyone who has ordered in the last 6 months, leaving only comanies that have not ordered.

 

Is this possible and if so how?

 

The measure I created is: 

M_LastOrder = FORMAT(Today()-SELECTCOLUMNS('Margin Reports',[DATE]),0)
This works fine until I ask for the latest date as a filter on the table visual, then it crashes the visual. I thought as a measure it would work based on the filter.
 
Any help would be greatfully received.
Need Help
  • mh2587's avatar
    mh2587
    1 year ago

    Create a Measure to Get the Latest Order Date:

    Latest Order Date = MAX('Margin Reports'[DATE])

    Calculate Days Since the Latest Order:

    Days Since Last Order = 
DATEDIFF([Latest Order Date], TODAY(), DAY)

    Filter the Data for the Last 6 Months:

    Orders Older Than 6 Months = 
IF([Days Since Last Order] > 180, 1, 0)

    Add Filters to the Table Visual:

    • Place Company Name and [Latest Order Date] in the table visual.
    • Add [Days Since Last Order] as a column to display the calculated days.
    • Use the [Orders Older Than 6 Months] measure in the visual-level filter and set it to 1 to display only companies that haven’t ordered in the last 6 months.

     

3 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    Create 2 measures

    Latest Order =
MAX ( 'Margin Reports'[Date] )

Days since last order =
DATEDIFF ( [Latest Order], TODAY (), DAY )
  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    1 year ago

    Create a Measure to Get the Latest Order Date:

    Latest Order Date = MAX('Margin Reports'[DATE])

    Calculate Days Since the Latest Order:

    Days Since Last Order = 
DATEDIFF([Latest Order Date], TODAY(), DAY)

    Filter the Data for the Last 6 Months:

    Orders Older Than 6 Months = 
IF([Days Since Last Order] > 180, 1, 0)

    Add Filters to the Table Visual:

    • Place Company Name and [Latest Order Date] in the table visual.
    • Add [Days Since Last Order] as a column to display the calculated days.
    • Use the [Orders Older Than 6 Months] measure in the visual-level filter and set it to 1 to display only companies that haven’t ordered in the last 6 months.

     

    • Rich_Wyeth's avatar
      Rich_Wyeth
      Icon for Helper I rankHelper I
      1 year ago

      Thank you! I hadn't done quite enough Measures. This works a treat.