Forum Discussion

jpt1228's avatar
jpt1228
Icon for Responsive Resident rankResponsive Resident
8 years ago
Solved

Filter Value based on the max date available

Hello, I typically try to exhast all the searches but I've been serching for a while and there are plenty of more complex examples but I can't get this figured out.   I have a data model and have b...
Need Help
  • MattAllington's avatar
    MattAllington
    8 years ago

    from the description you posted, the date you want is in the customer table and the filter is on the date table. So try this

     

    Measure = CALCULATE(SUM('Customer'[Dollars]), FILTER(ALL(DimDate[Date]) , DimDate[Date] = MAX(Customer[Date]))

MattAllington's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
8 years ago

from the description you posted, the date you want is in the customer table and the filter is on the date table. So try this

 

Measure = CALCULATE(SUM('Customer'[Dollars]), FILTER(ALL(DimDate[Date]) , DimDate[Date] = MAX(Customer[Date]))

  • jpt1228's avatar
    jpt1228
    Icon for Responsive Resident rankResponsive Resident
    8 years ago

    Hi MattAllington I see why I couldn't get it to work. I didn't think I had to use the date column in the table when I had a date table. Is there a way to write formula to filter the page to the latest date (Or max date).

     

    I have a dashboard that has visuals of sales by region, product, customer, warehouse etc.

     

    I would like to be able to filter all the visuals based on the max date that is in the table. And when new data is added I would like to change all the visuals to reflect the new data.

     

    These are all coming from the same 'Customer' table so the dates would be the same.

     

    Thanks

    • MattAllington's avatar
      MattAllington
      Icon for Community Champion rankCommunity Champion
      8 years ago

      You could create a calcualted column in your calendar table with a formula like =if(calendar[date]=MAX(dataTable[Date]),"latest")

       

      This will give you a column that you can filter on in your report.  The column only has 2 possible values, plus it is in a lookup table, so this calculated column is fine