dynamic "next date" selection?

[jd8766]

Hi, I have a data table that looks like the below.. I have the sold date linked to a calendar table and put into a slicer. I want to be able to select a date range in the slicer and the visual will only bring back the FIRST sold date for that company key (there are many company keys, I have just used one as an example)

So if I set my date range to 01 jan 2022 to 31 dec 2022 it would show me only contractkey 12


If I set the slicer 1 aug 2022 to 31 dec 2022 it would show me only contract key 13.. and so on.

Can anyone recommend if this is achievable and how to do in PBI? 

[v-rongtiep-msft · Accepted solution]

Hi @jd8766 ,

Please refer to my pbix file to see if it helps you. 

Create a calendar date first.

datetable = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))

Then create relationships between the tables.

Finally create a measure.

Measure =
CALCULATE (
    MIN ( 'Table'[sold date] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[companykey] = SELECTEDVALUE ( 'Table'[companykey] )
    )
)

If I have misunderstand your meaning, please provide more details.

Best Regards

Community Support Team _ Polly

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

[amitchandak]

@jd8766 , I think you need two measures like

Last Qty = Var _max = minx(filter( ALLSELECTED(Data1), Data1[ComponentKey] = max(Data1[ComponentKey]) ),Data1[soldDate])
return
CALCULATE(sum(Data1[Sold Amount]), filter( (Data1), Data1[ComponentKey] = max(Data1[ComponentKey]) && Data1[soldDate] =_max))

Sum Last Qty = sumx(VALUES(Data1[ComponentKey]) , [Last Qty])

refer the blog, you need first date

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...