Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
mottya
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

I failed to calculate the lost one. Please help.

Thanks

Table sample.png

4 REPLIES 4
mottya
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

mottya_0-1668711833126.png

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)
 

Hi, @mottya 

If your problem has been solved, you could accept the helpful answer as solution to close this thread. 
If you still need help, please share a sample file for further research.

Best Regards,
Community Support Team _ Eason

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.

daXtreme
Solution Sage
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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors