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.
Hello I am following up in a new thread the thred in the link below.
https://community.powerbi.com/t5/Desktop/Determine-if-date-is-between-2-dates/m-p/209365#M92340
I am trying to apply the formula below:
Column = CALCULATE ( VALUES ( Quarters[Value] ), FILTER ( 'Quarters', 'Quarters'[Start] <= EARLIER ( 'Calendar'[Date] ) && 'Quarters'[End] >= EARLIER ( 'Calendar'[Date] ) ) )
Everything works fine until the function EARLIER. Just after the EARLIER function when I am trying to type the date field from my calendar table I don't have the list of the available data fields to choose it. Can you please support with this?
Thanks.
Solved! Go to Solution.
Hi @Anonymous,
By my tests, the formula from AlB is also helpful, you could have a try with it.
In addition, from your image, it seems that your formula has another mistake, you missed a ")" after Values("SPECS","SPECS"[Columnname]); please correct it and try again.
Here is my attached test file which reproduces your scenario but I have no issue.
Best Regards,
Cherry
Hi @Anonymous,
Here is reference that you could use UNION function to achieve append with Dax.
More details, please refer to the attachment which incluing the detail steps.
Best Regards,
Cherry
Hi @Anonymous,
By my tests with that formula, it works as expected.
Could you share your data model and the image of the fomula so that I could understand your scenario better.
Best Regards,
Cherry
Hello,
Please find below the two tables I have. Here I am trying to match the date in "Date" table with the appropriate date range in the SPECS table (the same as the quarters table I mentioned before) and get the related outcome depending on the match.
In my formula I can only choose from the SPECS table and not from the DATE table, so I cannot choose the data field date to compare it with Time Start field.
The formula I have is the below
Hi @Anonymous,
By my tests, the formula from AlB is also helpful, you could have a try with it.
In addition, from your image, it seems that your formula has another mistake, you missed a ")" after Values("SPECS","SPECS"[Columnname]); please correct it and try again.
Here is my attached test file which reproduces your scenario but I have no issue.
Best Regards,
Cherry
Mant thanks @v-piga-msft,
Now it seems to work! I have another question for you before I close this thread. Now that I have applied all my formulas created in DAX in my report. I want to append all my tables into one table. The problem is that now the calculated fields do not appear in the consolidated table after this append. How can I bring all my calculated fields in the appended table? Please see below an example image of what I want to do.
Hi @Anonymous,
Here is reference that you could use UNION function to achieve append with Dax.
More details, please refer to the attachment which incluing the detail steps.
Best Regards,
Cherry
@Anonymous
Try the following:
Column = CALCULATE ( VALUES ( Specs[BU_LOW] ), FILTER ( 'Specs', 'Date'[Date] >= 'Specs'[Time Start] && 'Date'[Date] <= 'Specs'[Time Ended] ) )
You also need to check what you put on Values function> Is value field the correct one to put there
Hello,
Please find below the two tables I have. Here I am trying to match the date in "Date" table with the appropriate date range in the SPECS table (the same as the quarters table I mentioned before) and get the related outcome depending on the match.
In my formula I can only choose from the SPECS table and not from the DATE table, so I cannot choose the data field date to compare it with Time Start field.
The formula I have is the below
Hi @Anonymous
probably because you're filtering the Quarters table and there you cannot reference a column on the Calendar table. What row of that table are you referring to? None. So the engine cannot determine a value and therefore it is trying to avoid that you write it.
What are you trying to do exactly with Calendar[Date]? Maybe if you explain that we can help
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 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |