Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
Solved! Go to Solution.
@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 ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Donny620 it doesn't make sense at all, what is your data source? Are you using DQ/Import or Live connection?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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?
@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 ) )
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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!
@Donny620 did you look at pbix file with the solution, I used a different approach and you should follow the same.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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!
@Donny620 see attached, I hope that is what you are looking for, if not then let me know the expected output. Sorry for the delay.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
@Donny620 check my formula, it is not DATE1, DATE2, there is a parenthesis in between.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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)
Hi @durack99 thank you for your answer! Unforuntatly I for some reason can't add columns, only measures (and if I try to go to Transform Data I get "A DirectQuery connection is required"). Due to how the PBI is set up within my org. Is this task impossible if I can't create columns? Is there a measure that will work? Thanks!
@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 ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi 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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |