Forum Discussion

Ibadkhan's avatar
Ibadkhan
Icon for Helper III rankHelper III
5 years ago
Solved

Customization of filter.

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.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Ibadkhan
      Icon for Helper III rankHelper III
      5 years ago

      Anonymous  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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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.