The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all!
I'm very new to Power BI and Dax calculations. I know this is likely very simple! The examples I'm finding are more complex than I need and are confusing me more than helping. I'm have two data sources, one is a Reference table of dates (Payment Cycles), the other a list of outgoing payments (Payments). I need to find the date from the Payment table cycle table that is closest to the date in the Payment Cycle table, but is not after it.
Here's a sample of my data:
Thank you so much for any tips!
Solved! Go to Solution.
@Anonymous find the attached pbix provided by you and the measure performs as expected, not sure what is the issue here
@Anonymous just so I understand, you want to bring the closest
Payments[Due Date]
to the following table?
PaycycleDates
@smpa01 Yes that's correct!
@Anonymous you can use this emasure
Measure =
var _max = MAX(PaycycleDates[Payday])
var _val = CALCULATE(MAX(Payments[Due Date]),FILTER(Payments,Payments[Due Date]<=_max))
return _val
@smpa01 Thank you. This partrially works. If the date in both tables matches, then it matches perfectly. However, if the date is in the Payment table, but NOT in the Paycycle table, then I would like to find the date closest to the Paycycle date. Currently, it's just left blank. Thank you!
@Anonymous find the attached pbix provided by you and the measure performs as expected, not sure what is the issue here
@Anonymous provide sample data and expected output,betetr still provide a sample pbix. uplaod in 1/g drive and share the link here.
Hi,
Here is one way to do this:
Example data:
Dax:
Proud to be a Super User!
Thanks @ValtteriN I tried your suggestion but I received the following error message:
here's link to my Sample Files in case this helps.
Hi,
The Dax in my example was for a measure. If you place it within a column that type of error might occur due to reason explained here in SQLBI's post: https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/
Is it possible to use a measure instead of a column in your model?
Proud to be a Super User!
User | Count |
---|---|
11 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
23 | |
14 | |
13 | |
10 | |
8 |