CODEWITHSUNDEEP
powerbidax
Wouter
Wouter

Reputation: 2976

DAX max date in dimension ignore filters on date

In my Power BI model I have a fact table (factSales) that links to a date dimension (dimDate) through a surrogate key DateId. Now I want to add a measure to obtain the max invoice date for each client. But it should be the maximum date ignoring the context (for the date filters). (So if I filter all sales in Q1 2020, then I still want the max invoice date in e.g. 2021).

This is how I got it working:

Add new column in factSales:

Invoice Date = RELATED(DimDate[Date])

Add new measure in factSales:

Last Contract = 

            CALCULATE(     MAX(FactSales[Invoice Date]),
                                    ALL( DimDate )
                                )

This works, but is there a better way to do this ? Without the extra calculated column. (And without using both-directional filtering).

Upvotes: 0

Views: 14671

Answers (3)

AntrikshSharma
AntrikshSharma

Reputation: 661

You can use CROSSFILTER inside CALCULATE:

Last Contract =
CALCULATE (
    MAX ( Dates[Date] ),
    REMOVEFILTERS ( DimDate ),
    CROSSFILTER ( Sales[Date], Dates[Date], BOTH )
)

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40244

Try this:

Last Contract =
CALCULATE ( MAX ( DimDate[Date] ), ALL ( DimDate ), FactSales )

This removes filtering from a slicer on DimDate[Date] by still applies FactSales as a filter table.

Upvotes: 3

Kin Siang
Kin Siang

Reputation: 2699

You can create a measure to return max date using the following dax formula:

Measure = MAX(Sheet1[Date])

To always display the latest date without filter by slicer, you need to click on the slicer then goto Format >Edit Interaction >click none on the specific visual. In the following case, the max date is still 8 Nov 21 even though the slicer latest date is Sep 21

enter image description here

Upvotes: 0

Related Questions