Forum Discussion
Helper IDifference in date between two columns in a measure?
Hello!
I have an Excel version of this that I am trying to duplicate in PBI. I have three date columns Date 1a(stock date), Date1b(different stock date), and Date 2(sell date). I want to use Date 1a stock date over Date 1b stock date, so only use Date 1b if Date 1a is blank. The goal is to subtract them to see a days in inventory and then one more column which just says that if Aging is >540 then make it 540 and less than 5 make it 5.
For some reason the PBI I'm using won't let me create calculated columns (there are many tables which are connected to some sort of data lake, so I think I might not have access , e.g. right clicking on a table says 'new measure', 'select columns', etc but option to create column). I can create measures though. So my question is: is there a way to create measures which outputs an aging in the way as it is given below? Thanks!
| Date 1a (given) | Date 1b (given) | Date 2 (given) | Date 1 Column (if 1a is blank do 1b, otherwise always look at 1a) | Aging (Date 1 Column minus Date 2) | Trimmed Aging | |
| 6/21/2023 | 5/1/2023 | 4/6/2023 | 6/21/2023 | -65 | 5 | |
| Null | 11/30/2014 | 6/13/2019 | 11/30/2014 | 1656 | 540 |
17 Replies
- parry2k
Super User
Donny620 you can add a measure like this:
Trimmed Aging = VAR __Date1 = COALESCE ( SELECTEDVALUE ( Table[Date1] ), Table[Date2] ) VAR __Date2 = SELECTEDVALUE ( Table[Date2] ) VAR __Diff = DATEDIFF ( __Date1, __Date2, DAYS ) RETURN IF ( __Diff < 5, 5, IF ( __Diff > 540, 540, __Diff ) )
Donny620Hi thank you! I'm having trouble recreating that formula, when I get to
SELECTEDVALUE ( Table[Date1] ), Table[Date2] )
It won't let me select the second date2 column. I mean I can put in Date1 fine but when I try to put in Date2 it will only let me put calculations (measures?) and won't let me insert any real column.
Is this a limation of the SelectedValue function? Any ideas, does this make sense?
- Donny620
parry2k Thank you again! I'm having trouble duplicating the formula (Do I add another SelectedValue function and change 'Days' to 'Day'?) To make it super simple here are two files one an Excel which gives the
'answer' using the required logic and a pivot table, and one PBI which looks at the Excel and tries a formula like yours.
https://drive.google.com/file/d/1rRqD-bkrzi7TG2ioG_VpmeUZd_gM35wc/view?usp=drive_link
Thank you!!
You could always just use the Power Query through the 'Transform Data' Tab, if you had some thing like this:
The you could use a custom column to achieve what you want:
if [Date] = null and [Date2] <> null then [Date2] else if [Date] <> null and [Date2] = null then [Date] else if [Date] = null and [Date2] = null then null else List.Max({[Date],[Date2]}) - #duration(0,0,0,(Duration.TotalSeconds([Date]-[Date2]))/2)
- Donny620
Hi parry2k thank you so much and I'm so sorry but since my real data set is based on a SQL database it won't let me reference the columns in the way you did. I can get the formula to not give me an error if I tweak it as below but the result is not right (e.g. has values much greater than 540, etc.) My columns are stored in 'Fact Inventory'. I had to add the selectedvalue for it to let me select any column in that table (otherwise it only lets me select other measures), even though it may not be correct to do that.
Trimmed Aging =SUMX ('Fact Inventory',VAR __Date1 = COALESCE (selectedvalue('Fact Inventory'[Date 1a]), selectedvalue('Fact Inventory'[Date 1b] ))VAR __Date2 = selectedvalue('Fact Inventory'[Date 2 (date it leaves inventory)])VAR __Diff = DATEDIFF ( __Date1, __Date2, DAY )RETURNIF ( __Diff < 5, 5, IF ( __Diff > 540, 540, __Diff ) ))I'm hoping it's possible to just tweak this? 🙂 Please let me know thank you!
- Donny620
Hi I did thank you, that's where I got the formula (I tweaked it) for the measure I copied below. In your file I see a measure called "Trimmed Aging" (below) and I see a calculated column called "days in invnetory trimmed - fixed". Because my data is just a bunch of SQL tables, or perhaps because of access issues, I cannot create any columns, only measures. That's why I was hoping for a measure-only solution. 😞 Do you think this is possible? Thanks again!
- parry2k
Donny620 the measure I gave you is this one, don't know which one you are using:
Trimmed Aging = SUMX ( 'Data', VAR __Date1 = COALESCE ( Data[Date 1a], Data[Date 1b] ) VAR __Date2 = Data[Date 2 (date it leaves inventory)] VAR __Diff = DATEDIFF ( __Date1, __Date2, DAY ) RETURN IF ( __Diff < 5, 5, IF ( __Diff > 540, 540, __Diff ) ) )- Donny620
parry2k Agreed, what I mean is that the file I sent was based on sample data, but on my real data I am unable to use this measure as is because I can't reference columns in that measure. If I try to type in Data[Date 1a], it won't come up, it will only show me other measures/calculations. The only way it would let me reference it was with a function like selectedvalue. Does that make sense? 😞 Thanks!
- parry2k
Can you share what you expression are you using based on what I gave you. You are trying me to guess things here and making things over complicated.
- Donny620
So sorry! See below, where 'Fact Inventory' is the name of the SQL database table. I only added selectedvalue function because otherwise it won't let me insert/find columns like 'Date 1a' (it only 'finds' calculations/measures). Does this help?
Trimmed Aging =SUMX ('Fact Inventory',VAR __Date1 = COALESCE (selectedvalue('Fact Inventory'[Date 1a]), selectedvalue('Fact Inventory'[Date 1b] ))VAR __Date2 = selectedvalue('Fact Inventory'[Date 2 (date it leaves inventory)])VAR __Diff = DATEDIFF ( __Date1, __Date2, DAY )RETURNIF ( __Diff < 5, 5, IF ( __Diff > 540, 540, __Diff ) ))
