Need help on a Dax Measure

[TonyGu]

Here is a table I created. I would like to create a measure that return me the first date where type is 2.

For example, if in the date slicer, I select Januray 2022, it will return me the earliest next date for that agent that is type 2. In this case, here are the results I want to see:

Agent        Next Date

A               2022-02-01

B               2022-03-01

I will really appreciate if anyone can give me some hints.

[Anonymous · Accepted solution]

Hi  @TonyGu ,

Here are the steps you can follow：

1. Create calculated column.

Slice = FORMAT('Table'[Date],"mmmm")&" "&YEAR('Table'[Date])

2. Create measure.

Next Date =
var _select=SELECTEDVALUE('Table'[Slice])
var _currenttype=CALCULATE(SUM('Table'[Type]),FILTER(ALL('Table'),'Table'[Slice]=_select&&'Table'[Agent]=MAX('Table'[Agent])))
return
CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Agent]=MAX('Table'[Agent])&&'Table'[Type]=_currenttype+1))

3. Result:

Best Regards,

Liu Yang

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

[Jihwan_Kim]

Hi,

One of ways is creating this table in Power Query Editor before loading into the data model.

Please check the below picture and the attached pbix file's Power Query Editor.

let
    Source = Data_source,
    #"Grouped Rows" = Table.Group(Table.SelectRows(Source, each ([Type] = 2)), {"Agent"}, {{"Next Date", each List.Min([Date]), type nullable date}})
in
    #"Grouped Rows"

[Whitewater100]

Hello:

https://drive.google.com/file/d/1Ct0vWzig2dX5Q4FFG-8h0_4-tmsNRwFY/view?usp=sharing 

Here's one way. Theres a calculated column and measure.