topic Re: Formula for previous date in DAX Commands and Tips

Formula for previous date

jimpatel — Mon, 25 Jul 2022 15:19:29 GMT

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

Re: Formula for previous date

johnt75 — Mon, 25 Jul 2022 15:30:43 GMT

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

Re: Formula for previous date

jimpatel — Mon, 25 Jul 2022 15:50:50 GMT

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

Re: Formula for previous date

johnt75 — Mon, 25 Jul 2022 15:59:15 GMT

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

Re: Formula for previous date

jimpatel — Mon, 25 Jul 2022 16:05:43 GMT

Perfect

Thanks a lot for your help 🙂

Re: Formula for previous date

jimpatel — Tue, 26 Jul 2022 07:38:57 GMT

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

Re: Formula for previous date

johnt75 — Tue, 26 Jul 2022 08:03:34 GMT

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 )

Re: Formula for previous date

jimpatel — Tue, 26 Jul 2022 08:24:24 GMT

Perfect. Thanks a lot 🙂