Formula for previous date

[jimpatel]

Hi, Thanks for looking at my post.

I am trying to get formula for following logic please.

I have "Received Date" column, if today()-1 = Received date then show that row and  if today() fall on monday then show received date from friday,sat and sun. 

Any idea please?

Thanks

[johnt75 · Accepted solution]

You could create a measure like

Row is visible =
VAR startDate =
    SWITCH ( WEEKDAY ( TODAY (), 1 ), 2, TODAY () - 3, TODAY () - 1 )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Received date] ) >= startDate, 1 )

and then use that as a filter on your table or matrix visual, to only show when the value is 1

[jimpatel]

Hi,

Sorry for reopening this post. Formula works great, any idea of how to modify the formula for not showing today's date please?

Thanks a lot

[johnt75 · Accepted solution]

You could modify it like

Row is visible =
VAR startDate =
    SWITCH ( WEEKDAY ( TODAY (), 1 ), 2, TODAY () - 3, TODAY () - 1 )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Received date] ) >= startDate && SELECTEDVALUE('Table'[Received date]) < TODAY(), 1 )

[jimpatel]

Perfect. Thanks a lot 🙂

[jimpatel]

Perfect

Thanks a lot for your help 🙂

[jimpatel]

Thats perfect. Thanks a lot for your help. 

One question, why i cannot put measure in the filter? That is i used your formula and i am getting 1 for correct filter. I am thinking of putting filter with 1 and blank, in the report view, but it does not allow me to put the above measure in the filter. Any idea please?

Thanks

[johnt75]

You can't add measures as filters at the page or report level, only at the visual level.

If you have to have something at the report level then I think that would involve creating a new column in your model.

You could create a new column just to be used for filtering, using either Power Query or DAX, which returned the actual received date unless that date was a Friday or Saturday, in which case it would return the date of the following Sunday. You could then use that new date column as a filter, to only show values for yesterday. Once added to the filter, you could hide the column from the model to avoid confusion with the real received date

[johnt75 · Accepted solution]

You could create a measure like

Row is visible =
VAR startDate =
    SWITCH ( WEEKDAY ( TODAY (), 1 ), 2, TODAY () - 3, TODAY () - 1 )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Received date] ) >= startDate, 1 )

and then use that as a filter on your table or matrix visual, to only show when the value is 1