Customization of filter.

[Ibadkhan]

Hello everyone!

Hope you all are doing good.

I need some help related to filter. I have applied page level filter in my report. Basically our working days are monday to saturday and I have set the filter on page to last day. So on monday its showing sunday which has no record.  So basically I have to filter the page on last day and if last day is sunday then I want data of saturday because thats our last working day.

Is it possible?

Regards,

Ibad Khan.

[v-stephen-msft]

Hi @Ibadkhan ,

1.Create a separate date table.

Calendar = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

2.Create a measure to display the data of the last day.

LastDaySales = CALCULATE(SUM('Table'[Sales]),FILTER('Table',[Date]=SELECTEDVALUE('Calendar'[Date])-1))

3.The result is this. The date of the slicer is created with the date of a separate date table.

You can check more details from here.

Best Regards,

Stephen Tao

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

[Ibadkhan]

@v-stephen-msft  i have tried this already. Still facing the same issue. Its working correctly for all days except monday. On monday its showing blank because last day is sunday and there is no record of sunday. Therefore I want data of saturday on monday. 

Else in other words, If date contains sunday than day - 2 else day - 1.

[v-stephen-msft · Accepted solution]

Hi @Ibadkhan ,

You may try this LastDaySales measure.

LastDaySales =
IF (
    WEEKDAY ( SELECTEDVALUE ( 'Calendar'[Date] ), 2 ) = 1,
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', [Date] = SELECTEDVALUE ( 'Calendar'[Date] ) - 2 )
    ),
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', [Date] = SELECTEDVALUE ( 'Calendar'[Date] ) - 1 )
    )
)

08/03/2020 is Monday, and it shows the sales of Saturday.

You can check more details from here.

Best Regards,

Stephen Tao

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

[amitchandak]

@Ibadkhan ,

Refer to this blog. I have populated the last working day in front of the nonworking day. See if that helps in your logic. https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...