Forum Discussion
Using latest date based on two specific coloumns in data
Hi!
I have a question for a formula I would like to create. In theory it's only two dates which needs to be subtracted in order to provide time between 'Dispatch' and 'last sold date'
1. Can I do this with a calculation? or do I need to set up a table similar to a pivot table/matrix table I can then relate to my other data?
Formula wish: I want to know the time between 'Dispatch date' and 'last sold date' per Account ID, but I want to use the latest date per fruit and country for each calculation.
For instance:
For orange, account Konrad:
Use 'Date dispatch': 7/18/2013, however use last date for orange in his country Austria: 10/9/2013
I know this can be done in tableau by FIX to a specific column like country, but is there a way I can do this in DAX without needing to setup additional related tables?
Would appreciate input and help to this question. I have attached a test data set below
Thanks
| Fruit | Account ID | Country | Dispath | First sold date | Last sold date |
| Pear | klara | Austria | 10/23/2013 | 11/11/2013 | 2/10/2014 |
| Orange | Konrad | Austria | 7/18/2013 | 7/24/2013 | 8/5/2013 |
| Apple | Mike | Austria | 4/17/2013 | 4/25/2013 | 4/29/2013 |
| Orange | Pia | Austria | 8/7/2013 | 9/5/2013 | 10/9/2013 |
| Pear | Eva | France | 2/8/2014 | 3/17/2014 | 3/17/2014 |
| Orange | John | France | 8/28/2013 | 9/20/2013 | 9/20/2013 |
| Apple | Shannon | France | 5/29/2013 | 7/11/2013 | |
| Pear | Simon | France | 8/20/2013 | 9/18/2013 | 12/11/2013 |
| Pear | Chris | Germany | 1/29/2014 | 3/3/2014 | 3/3/2014 |
| Apple | Jim | Germany | 5/3/2013 | 7/8/2013 | 7/8/2013 |
| Pear | Liz | Germany | 7/2/2013 | 7/29/2013 | 10/29/2013 |
| Orange | Paul | Germany | 8/13/2013 | 8/23/2013 | 8/23/2013 |
| Apple | Beernard | Spain | 5/29/2013 | 6/4/2013 | 7/24/2013 |
| Pear | Karin | Spain | 8/21/2013 | 12/20/2013 | |
| Orange | Susan | Spain | 7/16/2013 | 7/22/2013 | 9/24/2013 |
| + 20 other | + 1000 other | + 20 other |
2. If this is not possible, how can I best set up a pivot like table in powerBI which I can then connect and use for this connection? I would like to have this table as part of the data model and based on my original table.
Like below
| Fruit | Country | Last sold date (max date) |
Here you go.
NewMeasure =
VAR dispdate =
MIN( T4[Dispath] )
VAR lastsoldthiscountry =
CALCULATE(
MAX( T4[Last sold date] ),
ALL( t4 ),
SUMMARIZE( t4, T4[Fruit], T4[Country] )
)
RETURN
IF(
NOT ( ISBLANK( lastsoldthiscountry ) ) && NOT ( ISBLANK( dispdate ) ),
INT( lastsoldthiscountry - dispdate )
)Pat
4 Replies
Please try this measure expression to get the results shown.
NewMeasure =
VAR dispdate =
MIN( T4[Dispath] )
VAR lastsoldthiscountry =
CALCULATE(
MAX( T4[Last sold date] ),
ALL( t4 ),
SUMMARIZE( t4, T4[Fruit], T4[Country] )
)
RETURN
IF(
NOT ( ISBLANK( lastsoldthiscountry ) ),
INT( lastsoldthiscountry - dispdate )
)Pat
Hi mahoneypat ,
This is exactly what I was looking for, and testing the formula in my massive dataset it runs very fast and smooth! 100000 thanks for that!
Last question: When no Dispatch date is available (e.g. above Pear Karin), can I have the formula to return blank since the date is missing?
Additionally in some cases I will have negative values returned, can I have the formula to blank those also?
Alternatively I could solve that either manually or by adding an additional step
Kristoffer
Here you go.
NewMeasure =
VAR dispdate =
MIN( T4[Dispath] )
VAR lastsoldthiscountry =
CALCULATE(
MAX( T4[Last sold date] ),
ALL( t4 ),
SUMMARIZE( t4, T4[Fruit], T4[Country] )
)
RETURN
IF(
NOT ( ISBLANK( lastsoldthiscountry ) ) && NOT ( ISBLANK( dispdate ) ),
INT( lastsoldthiscountry - dispdate )
)Pat
