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

IF function (involving dates calculations with related table)

Dear Power BI users,

I'm quite new to Power BI and I'd like to use some good knowledge to point me in the right direction.

I would like to replicate this basic excel IF function (involving dates calculations and related tables) to Power BI, but I'm facing some issues..

 

I'm creating a new column, the formula is accepted but the results are wrong, it shows me "NO" in every cases.


Using this formula:

Column = IFERROR(
             IF(
                  AND(
                    RELATED('Report Date Update'[Report Date Update])  <  Data[Valid until];
                            (Data[Valid until] - RELATED('Report Date Update'[Report Date Update]))  <   5*365);
           "Yes" ; "No"
              );"")

 

Report Date Update table has only one record, the report date itself (i.e. 28/9/2017). And has a relationship - Many to One / Single with the Data table.

 

Data[Valid until] it's Date formated column, and besides dates have a few blanks and some errors also, so I suppose I need to complement this function with the IFERROR statement.


Please let me know if some additional information is needed.

Thank you in advance!

 

2 ACCEPTED SOLUTIONS
Solution Specialist
Solution Specialist

So am I right in understanding you want to determine what records have a Valid Until Date that is in the future but no more than 5 years in the future? Do you have records in Data that meet this criteria?

 

Try replacing 'Report Date Update'[Report Date Update] with DATE(2017,9,28) in your formula just to rule out you have some issue with your relationships as this would contain your formula to just your Data table.

View solution in original post

Super User III
Super User III

Hi,

 

Try this

 

=IFERROR(IF(EDATE(RELATED('Report Date Update'[Report Date Update]),60)<=Data[Valid until],"Yes","No"),BLANK())

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Frequent Visitor

Thank you @erik_tarnvik and @Ashish_Mathur !

 

indeed there must be something wrong with the relationship, because i get the right result with DATE(2017,9,28), but not with related date table.

 

For now (and it seems to be working well at the moment) I'll use a formula with a bit of the contribution of both of you.

 

x = IF(
         AND(
            DATE(2017;08;29) < Data[Valid until];
            Data[Valid until] - EDATE(DATE(2017;08;29);60) <= Data[Valid until]);
     "yes"; "no"
        )

 

So thank you so much for pointing me in the right direction!!

 

Meanwile I will investigate the relationship between the two tables..

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User III
Super User III

Hi,

 

Try this

 

=IFERROR(IF(EDATE(RELATED('Report Date Update'[Report Date Update]),60)<=Data[Valid until],"Yes","No"),BLANK())

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Solution Specialist
Solution Specialist

So am I right in understanding you want to determine what records have a Valid Until Date that is in the future but no more than 5 years in the future? Do you have records in Data that meet this criteria?

 

Try replacing 'Report Date Update'[Report Date Update] with DATE(2017,9,28) in your formula just to rule out you have some issue with your relationships as this would contain your formula to just your Data table.

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors