Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Donny620
Helper I
Helper I

Difference 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/20235/1/20234/6/20236/21/2023-655 
Null11/30/20146/13/201911/30/20141656540 
1 ACCEPTED SOLUTION
parry2k
Super User
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 ) )

 

 

 



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.

View solution in original post

17 REPLIES 17
parry2k
Super User
Super User

@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.

parry2k
Super User
Super User

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?

 

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 )
        RETURN
            IF ( __Diff < 55IF ( __Diff > 540540, __Diff ) )
    )
parry2k
Super User
Super User

@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!

parry2k
Super User
Super User

@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!

parry2k
Super User
Super User

@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.

 

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 )
        RETURN
            IF ( __Diff < 5, 5, IF ( __Diff > 540, 540, __Diff ) )
    )
 
I'm hoping it's possible to just tweak this? 🙂 Please let me know thank you!
parry2k
Super User
Super User

@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

 

https://docs.google.com/spreadsheets/d/1O33eBH4uqJns0Fz-Ip7_22XXEfYQ9gMd/edit?usp=drive_link&ouid=10...

 

Thank you!!

@parry2k Any advice for me here? 🙂 

You could always just use the Power Query through the 'Transform Data' Tab, if you had some thing like this:

durack99_0-1694482098721.png

The  you could use a custom column to achieve what you want:

durack99_1-1694484001976.png

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!

Adsy
Frequent Visitor

Adsy_0-1694484223644.gif

parry2k
Super User
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 ) )

 

 

 



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?

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.