Forum Discussion
Helper ICalculated column is not returning the desired value using Calculate
Hello all.
I have two tables, equipment and snapshot. equipment has all assets, snapshot report on some issues with assets. snapshot has a date column called run_date, where the snapshot of the state of the assets was captured. equipment has a column called equipment_start_up_date, when the equipment was registered. both alos share a column called plant_key
Now, I want to create a calculated column in snapshot to capture how many new assets were created 30 days before the snapshot was captured. I created this:
Column =
countrows(equipment),
Filter(
all(equipment),
equipment[equipment_start_up_date] >= DATEADD(snapshot[run_date],-1,MONTH) &&
equipment[equipment_start_up_date] <= snapshot[run_date] &&
equipment[plant_key] = snapshot[plant_key]
)
)
30 days or one month? There's a difference...
DATEADD applies to the entire column. To shift individual dates you use EDATE.
7 Replies
- lbendlin
Super User
30 days or one month? There's a difference...
leandro_vegacrlbendlin , 1 month is the right thing, you are right.
This solves it beautifully. Thank you very much.
If it was not too much to ask, would you mind to clarify why my implementation gave a wrong calculation?
Again, thanks for your time and help.
- lbendlin
DATEADD applies to the entire column. To shift individual dates you use EDATE.
- leandro_vegacr
got it, thanks!
- leandro_vegacr
lbendlin I'm puzzled as to why I don't get the same result as you. In my dataset, both tables are related to a plant dimension and a date dimension, and plant key is a calculated column in snapshot. Finally, equipment has over 1,7 million rows.
Could any of this thing affect the outcome?
for example, for Dec 4, 2023, TC11BLO, you are getting 347 rows, which is the right amount. However, I get 0:
I'm seeing the filter is not taking into account the period of dates provided, only the specific day for that row, so it must find a perfect coincidence in plant and date to return anything.
Any idea?
Thanks again.
- lbendlin
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.- leandro_vegacr
I understand full data may be required, but I'm restricted to share it. I ended up creating a measure, based on your ideas and the measure is working fine for my purposes, so this is good enough. Again, realy thnakful for the effort.