Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi hopefully a quick query for a Guru.
i created a calculated column for an age in months as follows:
It works out the value correctly for me. No problem so far.
The issue I have though is I am using it in a table in which I have a "last entry date" column.
I want it to sum all the entries by a job reference and return the oldest date entry only.
I am finding that it is splitting the output for each individual time entry date. So for example
I get 5 entries of job number 1, but I want it return just one value of the oldest.
Its an Aged WIP table basically.
My last date entry measure is:
any ideas?
Solved! Go to Solution.
Hi, @Pmorg73
Sorry for not being able to reply you in time due to busy Monday.
If you use a calendar table date column in your visual, you can easily modify your measure.
Like this:
Measure =
MAXX (
FILTER ( 'Table', [job summary] = SELECTEDVALUE ( 'Table'[job summary] ) ),
RELATED ( 'Table 2'[Date] )
)
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Pmorg73 , Try a measure like
calculate(countrows(Table), filter(Table, Table[Ledger Date] = calculate(Min(Table[Ledger Date]), allexcept(Table, Table[JOB ID])))
you have calulate both sides of the equal sign? makes no sense
I tried to use both as two measures. First string returns the incorrect number. And the second string uses two columns. "Multiple columns cannot be converted to scalar value"
Back to the drawing board I think with this one
@Pmorg73 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Thanks for helping.
screen grab. I recreated my datediff in dax, so both a calc column and dax give me the correct month timeframe difference which is great.
If you see I have 4 entries for "40 Browns Bay", but I am after just a single value of the newest entry. So in this case it would be 28/7/20 and 6 months old. I do not need the 3 previous entries to show up. I just want to indicate when was the last time the job was worked on a time entry made in the system, and then sum up all of the "WIP" value for that job. If I remove the month age column it aggregates like this. What i want is just then a single column entry per job with the age of the last entry date only. I need to stop it showing all of the entries in that job. does that make sense?
Hi, @Pmorg73
According to your description, I think you can modify your measure to calculate the desired result.
Like this:
last date =
MAXX (
FILTER ( 'Table', [job summary] = SELECTEDVALUE ( 'Table'[job summary] ) ),
[date]
)
No of months old = -DATEDIFF(TODAY(), [last date], MONTH)
Here is my sample .pbix file.Hope it helps.
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the help and the logic. It does not seem to be working for me. I have a seperate date table, but using the measure I can not select the date column in that, only in my fact table.
data:
measure used:
result:
model:
any thoughts?
Hi, @Pmorg73
Sorry for not being able to reply you in time due to busy Monday.
If you use a calendar table date column in your visual, you can easily modify your measure.
Like this:
Measure =
MAXX (
FILTER ( 'Table', [job summary] = SELECTEDVALUE ( 'Table'[job summary] ) ),
RELATED ( 'Table 2'[Date] )
)
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Its curious. I can not get it to work
measure
result
I am still getting a result for each entry
Hi, @Pmorg73
Since you didn‘t provide sample data and files, the sample I made is to reproduce the content of your screenshots, and there may be some situations that are not mentioned and cause problems.
Is the 'total all wip' a measure? Can you provide the formula? Since you didn’t specify, I used it as a column directly in my sample.
You need to provide more information so I can help you.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
having drilled in. it appears when I add the "Age in Months" calculated column from my date table it is when it adds the multiple values. So I think you have solved my initial problem thank you. i will play around with a max value for the date AGE column as it is there that the problem lies.
This is my data table (its quite large and I dont want to share it as its business info)
this is the section of the model
Hi, @Pmorg73
Because the specific context is not clear,the result may be some unexpected circumstances. Anyway, I am glad you can modify it yourself.
If you have other questions, please feel free to ask me.
Best Regards
Janey Guo
No wrroies, I really appreciate your help btw
The Total WIP is a measure: I spot that I used ALL for another report so that I can could get a total ignoring the date slicing I had.......I will quickly check a new measure that is just sum
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |