topic Re: How to return a value from a column based on today's date? in DAX Commands and Tips

How to return a value from a column based on today's date?

Mark_ — Thu, 02 Feb 2023 20:10:14 GMT

Hi Everyone,

Hoping someone can help as I realy struggle with DAX and I can't seem to find a way of returning a value based on today's date. Specificaly I am trying to create a measure that will return the previous NR_Period based on todays date so that I can filter all visuals on the page by the last complete NR_Period. So if todays date was 24 July 2023 the DAX would return the NR_Period value of 202404 as that was the last complete period before todays date (table extract below).

It is worth noting that each NR_Period is normaly 28 days (4 weeks long) but not always depending on how the begining and end of the year falls.

Thanks for any help

Key	Date	Year	FinancialYear	Quarter	FinancialQuarter	Week	Month	MonthName	Period Number	Financial Year	NR_Period
20230701	07/01/2023	2023	2024	Q3	Q1	27	7	July	4	2024	202404
20230702	07/02/2023	2023	2024	Q3	Q1	27	7	July	4	2024	202404
20230703	07/03/2023	2023	2024	Q3	Q1	28	7	July	4	2024	202404
20230704	07/04/2023	2023	2024	Q3	Q1	28	7	July	4	2024	202404
20230705	07/05/2023	2023	2024	Q3	Q1	28	7	July	4	2024	202404
20230706	07/06/2023	2023	2024	Q3	Q1	28	7	July	4	2024	202404
20230707	07/07/2023	2023	2024	Q3	Q1	28	7	July	4	2024	202404
20230708	07/08/2023	2023	2024	Q3	Q1	28	7	July	4	2024	202404
20230709	07/09/2023	2023	2024	Q3	Q1	28	7	July	4	2024	202404
20230710	07/10/2023	2023	2024	Q3	Q1	29	7	July	4	2024	202404
20230711	07/11/2023	2023	2024	Q3	Q1	29	7	July	4	2024	202404
20230712	07/12/2023	2023	2024	Q3	Q1	29	7	July	4	2024	202404
20230713	07/13/2023	2023	2024	Q3	Q1	29	7	July	4	2024	202404
20230714	07/14/2023	2023	2024	Q3	Q1	29	7	July	4	2024	202404
20230715	07/15/2023	2023	2024	Q3	Q1	29	7	July	4	2024	202404
20230716	07/16/2023	2023	2024	Q3	Q1	29	7	July	4	2024	202404
20230717	07/17/2023	2023	2024	Q3	Q1	30	7	July	4	2024	202404
20230718	07/18/2023	2023	2024	Q3	Q1	30	7	July	4	2024	202404
20230719	07/19/2023	2023	2024	Q3	Q1	30	7	July	4	2024	202404
20230720	07/20/2023	2023	2024	Q3	Q1	30	7	July	4	2024	202404
20230721	07/21/2023	2023	2024	Q3	Q1	30	7	July	4	2024	202404
20230722	07/22/2023	2023	2024	Q3	Q1	30	7	July	4	2024	202404
20230723	07/23/2023	2023	2024	Q3	Q1	30	7	July	5	2024	202405
20230724	07/24/2023	2023	2024	Q3	Q1	31	7	July	5	2024	202405

Re: How to return a value from a column based on today's date?

FreemanZ — Thu, 02 Feb 2023 20:31:44 GMT

hi Mark_

Are you expecting something like:

Measure =

VAR _lastperiod =

MINX(

FILTER(

DateTable,

DateTable[Date] = TODAY()

DateTable[NR_Period]

) - 1

RETURN

CALCULATE(

[...],

DateTable[NR_Period]=_lastperiod

)

Re: How to return a value from a column based on today's date?

Mark_ — Thu, 02 Feb 2023 21:58:32 GMT

Hi FreemanZ

Thanks for replying but I am getting the following error:

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

This is what I entered, have I missed something?

Measure =

VAR _lastperiod =

MINX(

FILTER(

'Calendar',

'Calendar'[Date] = TODAY()

'Calendar'[NR_Period]

) - 1

RETURN

CALCULATE(

'Calendar',

'Calendar'[NR_Period]=_lastperiod

)

Re: How to return a value from a column based on today's date?

FreemanZ — Fri, 03 Feb 2023 01:02:38 GMT

hi Mark_

when you say "returning a value based on today's date", what value do you mean?

Re: How to return a value from a column based on today's date?

Anonymous — Fri, 03 Feb 2023 01:53:05 GMT

Hi Mark_ ,

Please try below steps:

1. below is my test table

Table:

2. create a measure with below dax formula

Measure = VAR cur_date = DATE ( 2023, 07, 24 ) VAR _a = CALCULATE ( MAX ( 'Table'[NR_Period] ), 'Table'[Date] = cur_date ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[Date] < cur_date && 'Table'[NR_Period] <> _a ) RETURN MAXX ( tmp, [NR_Period] )

3. add a table visual with measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to return a value from a column based on today's date?

Mark_ — Sat, 04 Feb 2023 17:06:17 GMT

Hi FreemanZ ,

So our calander is based on 4 week periods ratheer than months, which means we have 13 periods in a year. Not every Period is 4 weeks long depending on how the begining or end of the year falls as a Period must always finish on a Saturday and start on a Sunday. Period 1 and 13 need to start and end of the finicial year (31st March / 1st April). As a result we have a calander table that has every date for the year in one coloumn and the corresponsing period in another.

So what I need to be able to achieve, is to count the number of somethings (inspections or audits) that have happend in another table that were carried out in the last complete period without having to set manual filters or manualy update the report each time the period changes.

For example if Todays date is 4/2/2023 and the Period is Period 10 of the fincial year 2022-23 (expressed as 202310) then I need to the DAX to return a value for thee previous Period, Period 9 (202309) and then use that in the same measure to count the numbeer of specific inspections in another table.

Thanks.

what I am trying to do is count all the inspections that have happend in an

Re: How to return a value from a column based on today's date?

FreemanZ — Sun, 05 Feb 2023 00:50:07 GMT

hi Mark_

how are you going to use the expected measure? Or what context do you have for the measure? In a card visual? Table Visual? With a slicer?