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.
Hello Team,
Can anyone help me with the scenario or example related? I'm new to power bi and learning.
We had a requirement to create filter data for multiple columns.
The Scenario is companies get their contracts renewed for every 3 years. If they don't want to renew then it will be considered discontinued. Any help very much appreciated.
Columns as --Company Name,Date of issue,Status(Active,Expired,Discontinue),Category(New,End Of cycle-3years) ,ID'setc.
The issue is when the company has completed its contract for 3 years -and renewed.. they were generated with new IDs and categories as New.
For Example - Company A, Company B, Company C
Company A was created on the year 07/15/2019 -completed the 3 years cycle on 7/14/2022 -Their ID was given as ID A.Status is Expired.
Now Company A went for renewal in the year 2022. Their ID was generated New as !D A1.Status as Active and Category as New.
Similarly, other companies got discontinued in between, etc. New companies took new contracts.
How do I filter to know which company has completed 3 years of cycle and went for renewal?Which are new clients for this years etc.
Any help is Very much appreciated.Thank you in advance.
@Salesforce This would be far easier if you posted sample data per the articles below. You could determine which companies had completed a three year cycle by using SUMMARIZE by Company with a COUNTROWS and any row with 2 or more rows has at least been through a 3 year and renewal cycle. Clients that are new for this year could be done essentially like this:
Better Sales from New Customers =
VAR __CurrentCustomers = DISTINCT('Table'[ID])
VAR __MinDate = MIN('Table'[Date])
VAR __Table = FILTER(ALL('Table'),[Date] < __MinDate)
VAR __PreviousCustomers =
DISTINCT(
SELECTCOLUMNS(
__Table,
"ID",[ID]
)
)
VAR __NewCustomers = EXCEPT(__CurrentCustomers, __PreviousCustomers)
RETURN
SUMX(FILTER('Table',[ID] IN __NewCustomers),[Value])
This can roll-up to Year or you can replace [Date] with a [Year] column.
Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Hello @Greg_Deckler ,
Thanks for your response. I will implement the given formulae.
I tried this formula for 3 years completed.
cycle phase = if(Table[Date of Expiry].[year]=Table[Date of Initial registration].[year]+3,"1st cycle",if(Table[Date of Expiry].[year]=Table[Date of Initial registration].[year]+6,"2nd cycle","3rd cycle") ))
But this formula didnt work.Any help very much appreaciated.Thanks in Advance.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
13 | |
8 |
User | Count |
---|---|
75 | |
50 | |
47 | |
16 | |
13 |