Forum Discussion

MDodds's avatar
MDodds
Resolver II
3 years ago

Filter interacting with DAX Measure

Hi guys,

 

I am some Xero data pulling to report and a measure that displays the latest invoice amount which I then slice with customer data.

 

DAX measure below:

 

 

 

Last Invoice Amount = 
VAR LastSalesDate =
    CALCULATE (
        MAX ( 'Unique Invoices'[Date] ),
        FILTER ( 'Unique Invoices', 'Unique Invoices'[Type] = "ACCREC" )
    )
RETURN
    CALCULATE (
        [Total Invoice Amount],
        USERELATIONSHIP ( Dates[Date], 'Unique Invoices'[Date] ),
        FILTER ( Dates, Dates[Date] = LastSalesDate )
    )

 

 

 

 

The problem is I also slice the data by financial year. If I have the current financial year selected in the slicer, the above DAX works great. However if I have a previous financial year selected I get a blank result, obviously as the most recent invoice date is outside of that financial year.

Does anyone know how I could edit this to interact with the date slicer?

 

Cheers

Need Help

2 Replies

    • MDodds's avatar
      MDodds
      Resolver II
      3 years ago

      Thanks djurecicK2.

      This is about half way there. If I can't solve it fully I will go with this solution.
      This displays the most recent invoice in the data, but essentially ignores the slicers. I want it to still operate with the slicers, so if I select FY2022 for example, it shows the last invoice in that year, not the last invoice in the full dataset.