cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## Calculate gain and lost surgeons

Hi

I have a table that contains the surgeon and surgeries date

I want to calculate for each quarter how many surgeons I gained and lost

Gain - a surgeon performed surgery in the current quarter and did not perform it in the previous one

Lost - a surgeon did not perform any surgery in the current quarter and did in the previous one

I'm attaching a sample of my data table

Thanks

4 REPLIES 4
Frequent Visitor

I will clarify my problem.

When I calculate if a surgeon is lost and I don't have any case in the previous quarter then I can't see the quarter column in my table for that surgeon there for the measure is not calculating him as a lost one

As you can see in my picture Q2 is not showing because that person did not have any cases

This is my caclutaion for lost:

Lost = if([#Cases_This_Month]=0 && [#Cases_Prev_Month]>0,1,0)

Community Support

Hi, @mottya

If you still need help, please share a sample file for further research.

Best Regards,
Community Support Team _ Eason

Resolver I

Do you have a date table?  If so, you could use one measure as just
SUM(surgeries)
and another for

CALCULATE(SUM(Surgeries),
PARALLELPERIOD(DateTable[Dates], -3, MONTH)
)

That second measure will be showing values in current period if there were surgeries in previous period.  PARALLELPERIOD does have some pitfalls though that generally show by the period being expanded more than expected.  Tend towards using MONTH as the parameter rather than QUARTER or YEAR.  Basically use the smallest granularity that you will ever be required to report on.

As far as finding lost customers/surgeons, there are more elegant solutions to it, I prefer the one that uses set notation, but this should get you closer at least.

Solution Sage

This is a typical problem of New/Lost Customers (does not matter you're talking about surgeons - the pattern is the same). Please refer to this page www.daxpatterns.com where all of this and much more has been neatly presented. By the way, do not limit the time period to months only. Such a calculation should work for ANY period of time, meaning the measure should be always as general as possible.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors