keep filter ( ) + filter ( all ( ) )

[eddd83]

slicer: 

Date[Date] = Oct 24, 2020 (saturday)

Report:

Location	Product	Posted Price
Chicago	Apples	1.25
Chicago	Banana	1.75
Chicago	Carrots	1.61

Produce Table:

Location	Product	Posted Price	Date
Chicago	Apples	1.25	10/23/20
Chicago	Apples	2.12	10/24/20
Chicago	Apples	2.51	10/25/20
Chicago	Banana	1.75	10/23/20
Chicago	Banana	2.65	10/24/20
Chicago	Banana	2.75	10/25/20
Chicago	Carrots	1.61	10/23/20
Chicago	Carrots	2.45	10/24/20
Chicago	Carrots	2.41	10/25/20
Chicago	Mango	0.75	10/23/20

posted price =
VAR WeekDays =
    IF ( HASONEVALUE ( Dates[Date] ), VALUES ( Dates[Day Short] ), "Mon" )
VAR SelDate =
    IF ( HASONEVALUE ( Dates[Date] ), VALUES ( Dates[Date] ), TODAY () )
VAR PostedPricesWeekday =
    CALCULATE (
        MIN ( 'Posted Prices'[Price] ),
        'Posted Prices'[Price Date]
            = IF ( HASONEVALUE ( Dates[Date] ), VALUES ( Dates[Date] ), TODAY () )
    )
VAR PostedPricesSat =
    CALCULATE (
        MIN ( 'Posted Prices'[Price] ),
        KEEPFILTERS (
            FILTER (
                ALL ( 'Posted Prices'[Price Date] ),
                'Posted Prices'[Price Date] = SelDate - 1
            )
        )
    )
VAR PostedPricesSun =
    CALCULATE (
        MIN ( 'Posted Prices'[Price] ),
        KEEPFILTERS (
            FILTER (
                ALL ( 'Posted Prices'[Price Date] ),
                'Posted Prices'[Price Date] = SelDate - 2
            )
        )
    )
RETURN
    IF (
        WeekDays = "Sat",
        PostedPricesSat,
        IF ( WeekDays = "Sun", PostedPricesSun, PostedPricesWeekday )
    )

There's a many to 1 relationship between produce table [date] and date [date].

Even though the slicer says 10/24/2020, I want to show the last available weekday price, which is 10/23/2020 (friday) in this case. I have included filter ( all ( ) ) in my measure, otherwise, the 10/24/2020 filter context would only return the saturday value. However, I would still like to keep the filter context for location & product because otherwise my measure would return 0.75 (from mangos). I tried using Keepfilters, but i'm not achieving my desired result.

Help?

[smpa01 · Accepted solution]

@eddd83 not sure if you would like it, but I solved it using a different measure.

Revised Price := 
VAR _1 = MAX(Dates[Date])
VAR _2 = CALCULATE(MAX(Dates[Date]),FILTER(ALL(Dates),Dates[Date]<=_1&&[Max Revised date]<>0))
VAR _3 = CALCULATE([MaxPrice],ALL(Dates[Date]),Dates[Date]=_2)
RETURN _3

[smpa01]

@eddd83 did you try the solution provided?

[amitchandak]

@eddd83 , Not very clear. You can have a date table with following columns

WeekDay = WEEKDAY([Date],2) //monday
Start of Week = [Date] -[WeekDay]+1 //monday
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

Measure  =

var _max = maxx(allselected('Date'), 'Date'[Week Rank])

return

Calculate(min(Produce[Posted Price]) , filter(All('Date'), 'Date'[Week Rank]) =_max))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[eddd83]

@amitchandak 

sorry for the confusion. Please see the one drive link with my file:

https://onedrive.live.com/?authkey=%21APgzM8jL%2DrliMyw&cid=69E558AB6771FF6A&id=69E558AB6771FF6A%211...

Basically my measure (posted price) works for the weekdays, but returns a blank for the weekends. Ideally for the weekdays, it would show the previous friday (10/23) in this scenario.

[v-deddai1-msft]

Hi @eddd83 ,

You can create a new weekday column in your Date table :

weekday = WEEKDAY(Dates[Date],2)

Then use REMOVEFILTER to clear filter from slicer in [posted price]:

_posted price = var a = IF(SELECTEDVALUE(Dates[weekday])<=5,MAX(Dates[Date]),CALCULATE(MAX(Dates[Date]),FILTER(ALL(Dates),Dates[Date]<=MAX(Dates[Date])&&Dates[weekday]<=5))) return CALCULATE(MIN('Posted Prices'[Price]),'Posted Prices'[Price Date] = a,REMOVEFILTERS(Dates[Date])) 

For more details, please refer to the pbix file:  https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EUfmRSl0n3VLrP3tbk...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai