Dax/powerquery retrieve last date with a country based on restriction

[KristofferAJ]

Hi,
I have a data set containing:

Accounts selling various fruits in various countries, im working on some metrics and I really want to calculate the time between the first sold fruit in a country to the last sold fruit in the same country.

For instance: "days between" First sold ORANGE in Austria to Last sold ORANGE in AUSTRIA

I have solved this using a measure, which works but this doesn't allow me to work further with it as it throws me error with circular dependicies (ALL reference!)

I wonder if I can do this less complicated? an idea would be to set up an additional table in powerquery having latest date sold by fruit and country similar to what a pivot can do in excel, after that I could link to my main table and retrieve the date by using the RELATED option.

Any ideas?

Current muesure in powerBI:

DURATION =
VAR dispdate =
MIN( 'Test dat'[Dispath] )
VAR lastsoldthiscountry =
CALCULATE(
MAX( 'Test dat'[Last sold date] ),
ALL( 'Test dat' ),
SUMMARIZE( 'Test dat', 'Test dat'[Fruit], 'Test dat'[Country] )
)
RETURN
IF(
NOT ( ISBLANK( lastsoldthiscountry ) ) && NOT ( ISBLANK(dispdate) ),
INT( lastsoldthiscountry - dispdate )
)

Excel raw test:
https://www.dropbox.com/scl/fi/cppyzagm4ahusrxmadlg6/Test_data_withPivot.xlsx?dl=0&rlkey=2nf5wzl7etw...

PBI file:
https://www.dropbox.com/s/ljnyics6d7n74oc/test.pbix?dl=0

[tamerj1]

Hi @KristofferAJ 
You can use

Duration 2 = 
VAR FirstSoldDate = 
    SELECTEDVALUE ( 'Test dat'[Dispath] )
VAR LastSoldDate = 
    CALCULATE ( MAX ( 'Test dat'[Last sold date] ), ALLEXCEPT ( 'Test dat','Test dat'[Country],'Test dat'[Fruit] ) )
RETURN
    DATEDIFF ( FirstSoldDate, LastSoldDate , DAY )

[daXtreme]

Hi @tamerj1 

If you use ALLEXCEPT, then all the other filters will not be honoured in the measure. It might be what @KristofferAJ needs. It might not. ALLEXCEPT is a dangerous function/directive, by the way, and one should always carefully assess if using it is appropriate. Very often it is not. For instance, what happens if there are not any filters on Country and/or Fruit? What will it do?

[tamerj1]

@daXtreme 

I agree with you but in this case this is specifically required. 

[daXtreme]

This was written without me having seen the attached files... You might need to adjust this code slightly since I don't know what the model really looks like. Measures in PBI should work for any combination of attributes, therefore sometimes - or rather often - one has to extend the meaning of them. This is exactly what I've done here. Please read it carefully and try to understand how this works. Armed with this knowledge, you'll be better suited to make necessary changes. Thanks.

// If there are many countries visible
// in the current context and many fruits,
// then this measure will return the diff
// in time between the very first date a
// fruit was sold in any of the countries
// and the very last date of a fruit sold in any
// of those countries. If you make sure that,
// in the current context, only one country
// and one fruit are visible, then you'll get
// exactly what you're after - the diff between
// the date of the last date of the fruit and
// the first date of the fruit in this very country. 
// Bear in mind, though, that the measure below takes 
// into consideration the current period of time, 
// not the whole calendar. So, if you change the period,
// the diff will likely change as well.

[Duration (relative to period)] =
VAR FirstSoldDate =
    // min date in the current context
    MIN( 'Test dat'[Dispatch] )
VAR LastSoldDate = 
    // max date in the current context
    MAX( 'Test dat'[Dispatch] )
var Diff =
    // Assumption is that neither of the variables
    // above should be blank OR both should be
    // blank at the same time. If you do get 
    // a blank and the other is not, that means your
    // model should be revised.
    int( LastSoldDate - FirstSoldDate )
return
    Diff


// If you want to get the absolute duration,
// that is with no regard to the selected period
// of time (its end), then you'd write something
// like this:
[Duration (absolute)] =
VAR FirstSoldDate = 
    // This gets you the very first date of dispatch
    // (sold) keeping all the other filters intact.
    CALCULATE(
        MIN( 'Test dat'[Dispatch] ),
        REMOVEFILTERS( 'Test dat'[Dispatch] )
    )
VAR LastSoldDate = 
    // This gets you the very last sold/dispatch date
    // in the current context.
    MAX( 'Test dat'[Dispatch] )
var Diff =
    // Assumption is that neither of the variables
    // above should not be blank OR both should be
    // blank at the same time. If you do get 
    // a blank and the other is not, that means your
    // model should be revised.
    int( LastSoldDate - FirstSoldDate )
return
    Diff


// Remark:
// I can see you're doing everything in one table. Such
// models are simply WRONG. If you want to save yourself
// headaches and grief, please create a good dimensional
// model and then write measures. If you forgo this step,
// you'll be in trouble sooner rather than later.

By the way, if you want to learn about circular refs, please read or watch this: Avoiding circular dependency errors in DAX - SQLBI

If you have 'First sold date' in your data, the code above will even be easier. If you want to use this measure in a table, I'd advise not to do that. Instead, calculate your desired outcome in Power Query. It'll be not only easier. It'll be faster and there won't be any problems with circular refs.

There is a specific Power Query Community forum on which you can ask for help.

[KristofferAJ]

I would love to have this directly in powerquery, do you mean add a calculation in as custom column in the powerquery?

My data does have have everything, only issue I want to have the latest date sold per FRUIT/COUNTRY in order to establish how long total duration each type of fruit were per country.

[daXtreme · Accepted solution]

Here's one way to do it in Power Query. Don't forget that a query must start with "= let", so put "=" before the very first "let" in any of the queries/tables when you paste.

// Original Data
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVMxbsMwDPyL5wAiKdmSxizN2AIdjQwZjC5FYQTo/0upEXVK3UUwYZ6Odzyt6/R6v319bNNpkqTHZbvrybOj5ISEtPAMBS+O6be4ntbpbbvVfhI9X+pndrS0blFobAWN0PO+f26Vq/Cev8unOPKGHYrF0dyxNnPHkvIGo4rWrrfGf7DZZp5hzELVi2zaceb6730vTOyYgVYlNNrgqP7hUW5gG3kBY7VXJTSst1vRZfENOuyk0BLQHkFDaGJJHnNVqHckqFwOjEpNbPHJ424DxAI9frAGafMK4YgCnpVrwCaTyo1UAnZncFgdOIB6b+MmSIEMxTys1ZRyzJbFCDku4eo5ng+fQBZ7Ph5jz7jlMkX6g+1IHpbDsBz1Gh3ur4dAbvemuNaj2Pb25HFf7ALhE/+0KzmgJdtsRHlDiNMAtSRGU1vW0D3Gd6e6zePrDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, AccountID = _t, Country = _t, Dispatch = _t, #"First Sold" = _t, #"Last Sold" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Dispatch", type date}}, "en-GB"),
    #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"First Sold", type date}}, "en-GB"),
    #"Changed Type with Locale2" = Table.TransformColumnTypes(#"Changed Type with Locale1", {{"Last Sold", type date}}, "en-GB")
in
    #"Changed Type with Locale2"

// Durations
let
    Source = Table.Buffer(#"Original Data"),
    #"Grouped Rows" = Table.Group(Source, {"Fruit", "Country"}, {{"MinFirstSold", each List.Min([First Sold]), type nullable date}, {"MaxLastSold", each List.Max([Last Sold]), type nullable date}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Fruit", Order.Ascending}, {"Country", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Duration", each Duration.Days( [MaxLastSold] - [MinFirstSold] )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"MinFirstSold", "MaxLastSold"})
in
    #"Removed Columns"

// Data with Durations
let
    Source = Table.NestedJoin(#"Original Data", {"Fruit", "Country"}, Durations, {"Fruit", "Country"}, "Data with Duration", JoinKind.LeftOuter),
    #"Expanded Data with Duration" = Table.ExpandTableColumn(Source, "Data with Duration", {"Duration"}, {"Duration"})
in
    #"Expanded Data with Duration"

Just copy this code as-is into Power Query and paste it straight into the left-hand side panel where the names of the tables/queries normally are found. You can then disable the load of Original Data and Durations. Only load the Data with Durations table. First, check out Original Data. Then the helper table, Durations. Then check out the definition of Data with Durations. This should give you an idea how to do it in your case.

Here's another way to do it in PQ (simpler as it uses just one query):

// Original Data
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVMxbsMwDPyL5wAiKdmSxizN2AIdjQwZjC5FYQTo/0upEXVK3UUwYZ6Odzyt6/R6v319bNNpkqTHZbvrybOj5ISEtPAMBS+O6be4ntbpbbvVfhI9X+pndrS0blFobAWN0PO+f26Vq/Cev8unOPKGHYrF0dyxNnPHkvIGo4rWrrfGf7DZZp5hzELVi2zaceb6730vTOyYgVYlNNrgqP7hUW5gG3kBY7VXJTSst1vRZfENOuyk0BLQHkFDaGJJHnNVqHckqFwOjEpNbPHJ424DxAI9frAGafMK4YgCnpVrwCaTyo1UAnZncFgdOIB6b+MmSIEMxTys1ZRyzJbFCDku4eo5ng+fQBZ7Ph5jz7jlMkX6g+1IHpbDsBz1Gh3ur4dAbvemuNaj2Pb25HFf7ALhE/+0KzmgJdtsRHlDiNMAtSRGU1vW0D3Gd6e6zePrDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, AccountID = _t, Country = _t, Dispatch = _t, #"First Sold" = _t, #"Last Sold" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Dispatch", type date}}, "en-GB"),
    #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"First Sold", type date}}, "en-GB"),
    #"Changed Type with Locale2" = Table.TransformColumnTypes(#"Changed Type with Locale1", {{"Last Sold", type date}}, "en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale2", {"Fruit", "Country"}, {{"AllRows", each _, type table [Fruit=nullable text, AccountID=nullable text, Country=nullable text, Dispatch=nullable date, First Sold=nullable date, Last Sold=nullable date]}, {"MaxSoldDate", each List.Max([Last Sold]), type nullable date}, {"MinSoldDate", each List.Min([First Sold]), type nullable date}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Duration", each Duration.Days([MaxSoldDate] - [MinSoldDate])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"MaxSoldDate", "MinSoldDate"}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"AccountID", "Dispatch", "First Sold", "Last Sold"}, {"AccountID", "Dispatch", "First Sold", "Last Sold"})
in
    #"Expanded AllRows"