cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

DAX: Return a date value from a column that is closest to another date value from a row

Little stuck with this one.

I need to return the closest date value from [Last Pay Period Paycheck Column] that is <= the value within each row from column [End of Next Month]

 

Example: [End of Next Month] = 2/29/2020

                The closest date value in [Last Pay Period Paycheck Date] that is <= 2/29/2020 woud be 2/28/2020:

               Expected Result would be:      [Test] = 2/28/2020

 

See Image below for reference and current outputs:

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: DAX: Return a date value from a column that is closest to another date value from a row

Is this what you need? It is a calculated column. You didnt' specify but you are showing a table.

 

Test = 
VAR NextMonthDate = 'Table'[End of Next Month]
RETURN
MAXX(
    FILTER(
        'Table',
        'Table'[Last Payperiod Check Date] <= NextMonthDate
    ),'Table'[Last Payperiod Check Date]
)

 

2020-06-02 16_23_31-Untitled - Power BI Desktop.png

If not, can you post some actual data with expected results using the directions below?

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
Highlighted
Super User II
Super User II

Re: DAX: Return a date value from a column that is closest to another date value from a row

Is this what you need? It is a calculated column. You didnt' specify but you are showing a table.

 

Test = 
VAR NextMonthDate = 'Table'[End of Next Month]
RETURN
MAXX(
    FILTER(
        'Table',
        'Table'[Last Payperiod Check Date] <= NextMonthDate
    ),'Table'[Last Payperiod Check Date]
)

 

2020-06-02 16_23_31-Untitled - Power BI Desktop.png

If not, can you post some actual data with expected results using the directions below?

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Highlighted
Frequent Visitor

Re: DAX: Return a date value from a column that is closest to another date value from a row

That is exactly what I needed, thank you sir!

Highlighted
Super User II
Super User II

Re: DAX: Return a date value from a column that is closest to another date value from a row

Excellent @mpicca13 

Glad to help out!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors