Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
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:
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
29 | |
14 | |
11 | |
10 | |
9 |