IF function (involving dates calculations with related table)

[claugaspar]

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!

[erik_tarnvik · Accepted solution]

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.

[claugaspar]

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..

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted solution]

Hi,

Try this

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

Hope this helps.

[erik_tarnvik · Accepted solution]

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.