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

Question

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=2nf5wzl7etwr2hqg2lh4oyifx

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

user_2872922 · Answer

If I understand you correctly, you can do what you want entirely in Power Query.

Group by Fruit and Country
Aggregate by Min of Start Date and Max of End Date
Add column to calculate the Days difference:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Fruit", type text}, {"Account ID", type text}, {"Country", type text}, 
        {"Sold amount", Int64.Type}, {"Dispath", type date}, {"First sold date", type date}, {"Last sold date", type date}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Fruit", "Country"}, {
        {"First Date Sold", each List.Min([First sold date]), type nullable date}, 
        {"Last Date Sold", each List.Max([Last sold date]), type nullable date}}),
    
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Fruit", Order.Ascending}, {"Country", Order.Ascending}}),
    
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Days Sold", each 
        Duration.Days([Last Date Sold]-[First Date Sold]),Int64.Type)
in
    #"Added Custom"

The negative number for France Apples seems due to a data entry error

If there is a missing date entry, the table will show a null, due to how PQ processes nulls in arithmetical operations

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

1 Answers