CODEWITHSUNDEEP
powerbidax
m_slaav
m_slaav

Reputation: 17

Constant max date measure

So I have date table with all dates day by day related with salesdata table with sctructure basically like: region, branch, user, date, product, saleVolume So multiple dates per user. I have also date slicer in report page. I am in need of a measure, that will give constant max date depending on slicer selection and slicer selection only. For every possible combination of row/column in matrix visualisation - I want the same max date in every cell. Every combination of calculate(max/lastdate(),all/allselected/removefilter()) I tried resulted in giving max date per region/branch/user in data table, so if I select 01.01 - 10.01 range in slicer, but user's last sale was 05.01, the measure returns 05.01 instead of 10.01.

Upvotes: 0

Views: 1739

Answers (1)

Marcus
Marcus

Reputation: 4005

You could try something simple like:

Max Slicer Date :=
CALCULATE ( 
    MAX ( 'Date'[Date] ) ,
    ALLEXCEPT ( 'Date', 'Date'[Date] )
)

Edit: Here is some sample data that shows this is exactly what the measure does:

enter image description here

Edit 2: If you are for some reason using a bidirectional relationship between 'Date'[Date] and 'Sales'[Date] (not sure why one would do that in the first place, it is not a good idea and I strongly recommend avoiding such modeling philosophies) then you can amend the measure like so:

Max Slicer Date :=
CALCULATE ( 
    MAX ( 'Date'[Date] ) ,
    ALLEXCEPT ( 'Date', 'Date'[Date] ) ,
    CROSSFILTER ( 'Date'[Date] , 'Sales'[Date] , NONE )
)

Upvotes: 0

Related Questions