cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Comparing GroupedBy Dates

I have a list of customers and the dates of their appointments like so:

 Customer ID AptDate 111 1/1/2019 111 1/5/2019 222 2/3/2020 444 2/4/2020 555 2/5/2020 555 2/6/2020 555 2/7/2020 888 2/8/2020 999 2/9/2020 999 2/10/2020 999 2/11/2020

I'm trying to figure out how to keep track of the success rate of scheduling customers next appointment when they visit.  Successful Dataset would look like this (1 = NextAptScheduled):

 Customer ID AptDate (Past & Future) NextAptScheduled 111 1/1/2019 1 111 5/1/2021 0 222 2/3/2020 0 444 2/4/2020 0 555 2/5/2020 1 555 2/6/2020 1 555 2/7/2020 0 888 2/8/2020 0 999 2/9/2020 1 999 2/10/2020 1 999 2/11/2022 0

I'm thinking it makes sense to group the customers by their ID's with the dates of their purchases, but am having a hard time figuring if I should do that with a groupby, summarize,  or summarizecolumns, and how to work with the dates in their respective groups to achieve the goal from there.

Any help would be greatly appreciated. Thank you!

1 ACCEPTED SOLUTION
Super User

Hi,

This calculated column formula works

``=1*(not(CALCULATE(MAX(Data[AptDate (Past & Future)]),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])))=Data[AptDate (Past & Future)]))``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
6 REPLIES 6
Super User

Hi,

This calculated column formula works

``=1*(not(CALCULATE(MAX(Data[AptDate (Past & Future)]),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])))=Data[AptDate (Past & Future)]))``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Microsoft Employee

You can use this expression for a new Column on your Appointments table (replace with your actual table name).  I added a comment too in case you need the measure version in a table visual with your customer ID and Date columns.

NextApptScheduled =
VAR vThisDate = Appointments[AptDate]  //use MIN(Appointments[AptDate]) if a measure is needed instead
RETURN
IF (
ISBLANK (
CALCULATE (
COUNTROWS ( Appointments ),
ALLEXCEPT (
Appointments,
Appointments[Customer ID]
),
Appointments[AptDate] > vThisDate
)
),
0,
1
)

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Super User

Hi @czuniga

Please supply some sample data and your expected result so we can see what we're working with.

Regards

Phil

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

Helper III

Thanks for the suggestion. Done!

Super User

@czuniga , expected output is not clear . But these columns can help

rank Column for dates = rankx(filter(Table, [Customer] = earlier([customer]) ), [Date],,asc)

last purcahse date date = maxx(filter(Table, [Customer] = earlier([customer]) && [Date] = earlier([Date])),[Date])

Helper III

Tried again to explain what I'm trying to accomplish as I wasn't able to get what you suggested work. Appreciate the input though!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors