convert a measure into a column in powerquery

[KristofferAJ]

I have this measure which I consider moving into powerquery as it makes some problems for me as a measure.

Can a measure be created as a column in powerquery? or du I have want to re think the way I want to retrieve this data?

My goal is to be able tro get the duration from First fruit [FRUIT]  sold in country to last fruit sold in country... (the country duration)

Time Duration fruit sold =
VAR dispdate =
    MIN ( CTL[Site RTE Actual] )
VAR lastsoldthiscountry =
    CALCULATE (
        MAX ( CTL[Site Last Actual] ),
        ALL ( CTL ),
        SUMMARIZE ( CTL, CTL[FRUIT], CTL[Country Name] )
    )
RETURN
    IF (
        NOT ( ISBLANK ( lastsoldthiscountry ) ) && NOT ( ISBLANK ( dispdate ) ),
        INT ( lastsoldthiscountry - dispdate )
    )

Excel data:
https://www.dropbox.com/s/05a70bzjms8cqdu/Test_data.xlsx?dl=0

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

Alternatively

[sturlaws · Accepted solution]

Try this

[v-rzhou-msft · Accepted solution]

Hi @KristofferAJ ,

You just need to create a custom column in Power Query Editor.

let _lastsoldthiscountry = 
List.Max(
    let country = [Country],
        fruit = [Fruit]
    in Table.SelectRows(#"Changed Type", 
    each [Country] = country and [Fruit] = fruit)[Last sold date]),
    _dispath = [Dispath]
in 
if _lastsoldthiscountry <> null and _dispath <> null
then Number.From( _lastsoldthiscountry - _dispath)
else null

Then change the date type to whole number and the result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KristofferAJ]

This is SPOT ON, thanks for that!

[sturlaws]

Hi, @KristofferAJ, 

that depends on your scenario. If you need the measure to respond to filtering in the report, the answer is No. Otherwise it is in principle doable.

Cheers,
Sturla

[ghoshabhijeet]

@KristofferAJ  Please provide the below details. Thanks !

1. Sample data or PBIX file with sample data

2. Expected Output

[KristofferAJ]

Hi @ghoshabhijeet  and @sturlaws 
Thanks for your response, I have updated with data.

This might not be as complicated as I make it.

My main goal is to be able to calculate the longest time between fruits sold per country.

But I want to have the calculation to restrict to the type of fruit and country.

For instance, the time between first dispatched ORANGE in germany to last sols ORANGE in Germany.

This measure does the job, but needing this calculation to other things I run into issues with circular dependicies and I'm considering doing it differently, either

1. As a column in powerquery with the hope it will be possible
2. Consider as a new table having powerquery to to make a column with MAX date per fruit per country which I can get by 'RELATED'

I hope these thoughts makes sense

[sturlaws · Accepted solution]

Try this