Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
Hi,
Try this
=IFERROR(IF(EDATE(RELATED('Report Date Update'[Report Date Update]),60)<=Data[Valid until],"Yes","No"),BLANK())
Hope this helps.
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.
Hi,
Try this
=IFERROR(IF(EDATE(RELATED('Report Date Update'[Report Date Update]),60)<=Data[Valid until],"Yes","No"),BLANK())
Hope this helps.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |