topic Re: Measure that shows dates thru yesterday in DAX Commands and Tips

Measure that shows dates thru yesterday

Del235 — Thu, 21 Sep 2023 00:54:21 GMT

Today is 9/20/2023. I'd like to create a measure that would show All dates thru yesterday. I.E. 1/1, 1/2 ... 9/18, 9/19. It would always reference what today is and show all dates thru yesterday.

Thanks.

Re: Measure that shows dates thru yesterday

some_bih — Thu, 21 Sep 2023 12:01:52 GMT

Hi Del235

Measure is usually something which is aggregrable, like sum, min...

When you wrote all the dates up to yesterday, you mean table with all dates in ths year up to yesterday? Or...

Re: Measure that shows dates thru yesterday

Del235 — Thu, 21 Sep 2023 13:57:51 GMT

My apologies... Table with all dates in this year up to yesterday.

Re: Measure that shows dates thru yesterday

some_bih — Thu, 21 Sep 2023 14:20:52 GMT

Hi Del235 your cal. table could be

Test =

VAR __curent_year = YEAR(TODAY())

VAR __yesterday = TODAY()

RETURN

FILTER(

'Date',

YEAR('Date'[Date]) = __curent_year &&

'Date'[Date]< __yesterday

)

Did I answer your question? Kudos appreciated / accept solution!

Re: Measure that shows dates thru yesterday

Del235 — Thu, 21 Sep 2023 14:40:50 GMT

I have a Date table. The formula is now

Test = VAR __curent_year = YEAR ( TODAY ()) VAR __yesterday = TODAY () RETURN FILTER ( 'DateTable', YEAR ('DateTable'[Date]) = __curent_year && 'DateTable'[Date]< __yesterday )

I get the follow error:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Re: Measure that shows dates thru yesterday

some_bih — Thu, 21 Sep 2023 15:14:25 GMT

Hi Del235 I wrote you solution as new calculated table, not column.

Do you need column in your Date table to mark somehow dates in current year less than yesterday?

Re: Measure that shows dates thru yesterday

Del235 — Thu, 21 Sep 2023 16:28:39 GMT

I will check

Re: Measure that shows dates thru yesterday

Del235 — Thu, 21 Sep 2023 19:46:27 GMT

I get the following error:

Feedback Type:
Frown (Error)

Error Message:
'approx_count_distinct' supports only field access.

Re: Measure that shows dates thru yesterday

some_bih — Thu, 21 Sep 2023 20:23:51 GMT

Hi Del235 there is no measure in my proposed solution so please check your error.

Re: Measure that shows dates thru yesterday

Del235 — Thu, 21 Sep 2023 21:37:06 GMT

Error appears but the table is created as Test. The table shows every date in 2023 but it doesn't stop at 9/20, which would have been yesterday. Which field should I be using?

Re: Measure that shows dates thru yesterday

some_bih — Fri, 22 Sep 2023 05:25:16 GMT

Hi Del235 try just to rename table which is not Test. If it not works, please share your Date table details.