Difference in date between two columns in a measure?

[Donny620]

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

[parry2k]

@Donny620 it doesn't make sense at all, what is your data source? Are you using DQ/Import or Live connection?

[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 )

        RETURN

            IF ( __Diff < 5, 5, IF ( __Diff > 540, 540, __Diff ) )

    )

[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]

@Donny620 did you look at pbix file with the solution, I used a different approach and you should follow the same. 

[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 see attached, I hope that is what you are looking for, if not then let me know the expected output. Sorry for the delay.

[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 )

        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]

@Donny620 check my formula, it is not DATE1, DATE2, there is a parenthesis in between.

[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

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

Thank you!!

[Donny620]

@parry2k Any advice for me here? 🙂 

[durack99]

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

[parry2k · Accepted 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 ) )

[Donny620]

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?