cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
claugaspar
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
erik_tarnvik
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

Ashish_Mathur
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
claugaspar
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/
Ashish_Mathur
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

erik_tarnvik
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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.