Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Mark_
Frequent Visitor

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

‎02-02-2023 12:10 PM

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

 

KeyDateYearFinancialYearQuarterFinancialQuarterWeekMonthMonthNamePeriod NumberFinancial YearNR_Period
2023070107/01/202320232024Q3Q1277July42024202404
2023070207/02/202320232024Q3Q1277July42024202404
2023070307/03/202320232024Q3Q1287July42024202404
2023070407/04/202320232024Q3Q1287July42024202404
2023070507/05/202320232024Q3Q1287July42024202404
2023070607/06/202320232024Q3Q1287July42024202404
2023070707/07/202320232024Q3Q1287July42024202404
2023070807/08/202320232024Q3Q1287July42024202404
2023070907/09/202320232024Q3Q1287July42024202404
2023071007/10/202320232024Q3Q1297July42024202404
2023071107/11/202320232024Q3Q1297July42024202404
2023071207/12/202320232024Q3Q1297July42024202404
2023071307/13/202320232024Q3Q1297July42024202404
2023071407/14/202320232024Q3Q1297July42024202404
2023071507/15/202320232024Q3Q1297July42024202404
2023071607/16/202320232024Q3Q1297July42024202404
2023071707/17/202320232024Q3Q1307July42024202404
2023071807/18/202320232024Q3Q1307July42024202404
2023071907/19/202320232024Q3Q1307July42024202404
2023072007/20/202320232024Q3Q1307July42024202404
2023072107/21/202320232024Q3Q1307July42024202404
2023072207/22/202320232024Q3Q1307July42024202404
2023072307/23/202320232024Q3Q1307July52024202405
2023072407/24/202320232024Q3Q1317July52024202405

 

 

 

Message 1 of 7
1,436 Views
0
6 REPLIES 6
Anonymous
Not applicable

‎02-02-2023 05:53 PM

Hi @Mark_ ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1675389087744.png

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

vbinbinyumsft_2-1675389166685.png

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.

How to return a value from a column based on today's date.pbix
Message 7 of 7
1,354 Views
0
FreemanZ
Super User
Super User

‎02-02-2023 12:31 PM

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
)
Message 2 of 7
1,396 Views
0
Mark_
Frequent Visitor
In response to FreemanZ

‎02-02-2023 01:58 PM

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
)
Message 3 of 7
1,384 Views
0
FreemanZ
Super User
Super User
In response to Mark_

‎02-02-2023 05:02 PM

hi @Mark_ 

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

Message 4 of 7
1,365 Views
0
Mark_
Frequent Visitor
In response to FreemanZ

‎02-04-2023 09:06 AM

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

Message 5 of 7
1,306 Views
0
FreemanZ
Super User
Super User
In response to Mark_

‎02-04-2023 04:49 PM

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?

Message 6 of 7
1,263 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All