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.
Hi all,
I am relatively new to Power BI and DAX and I am having some issue with the following data. I would like to find out which relation numbers have ended in the previous year. However, some relation number have received a new license number so they should not be included.
To illustrate, relation number 1829 had a license number that ended in 31-dec-2017, but they received a new license number.
The relation number 1444 had a license number that ended in 31-dec-2017 and did not receive a new license number. I would like to filter on those relation number that had their license number ended and did not receive a new one.
License number | Relation number | Starting date | Ending date |
9809 | 1829 | 01-jan-10 | 31-dec-17 |
11907 | 1829 | 01-jan-18 | 31-dec-30 |
10023 | 2509 | 01-jan-12 | 31-dec-30 |
8338 | 1444 | 01-jan-08 | 31-dec-17 |
15222 | 3898 | 01-jan-15 | 31-dec-32 |
5944 | 983 | 01-jan-06 | 31-dec-15 |
14888 | 983 | 01-jan-16 | 31-dec-28 |
This is the preferred output:
License number | Relation number | Starting date | Ending date | Broken relation |
9809 | 1829 | 01-jan-10 | 31-dec-17 | 0 |
11907 | 1829 | 01-jan-18 | 31-dec-30 | 0 |
10023 | 2509 | 01-jan-12 | 31-dec-30 | 0 |
8338 | 1444 | 01-jan-08 | 31-dec-17 | 1 |
15222 | 3898 | 01-jan-15 | 31-dec-32 | 0 |
5944 | 983 | 01-jan-06 | 31-dec-15 | 0 |
14888 | 983 | 01-jan-16 | 31-dec-28 | 0 |
Thanks in advance.
Steef
Solved! Go to Solution.
@Anonymous Please try this as a "New Column"
BrokenFlag = IF(CALCULATE(COUNTROWS(Test157),FILTER(ALL(Test157),Test157[RelationNumber]=EARLIER(Test157[RelationNumber]) && Test157[EndDate] > TODAY()))>0,0,1)
Proud to be a PBI Community Champion
Hi @Anonymous,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
Hi all,
I am relatively new to Power BI and DAX and I am having some issue with the following data. I would like to find out which relation numbers have ended in the previous year. However, some relation number have received a new license number so they should not be included.
To illustrate, relation number 1829 had a license number that ended in 31-dec-2017, but they received a new license number.
The relation number 1444 had a license number that ended in 31-dec-2017 and did not receive a new license number. I would like to filter on those relation number that had their license number ended and did not receive a new one.
License number | Relation number | Starting date | Ending date |
9809 | 1829 | 01-jan-10 | 31-dec-17 |
11907 | 1829 | 01-jan-18 | 31-dec-30 |
10023 | 2509 | 01-jan-12 | 31-dec-30 |
8338 | 1444 | 01-jan-08 | 31-dec-17 |
15222 | 3898 | 01-jan-15 | 31-dec-32 |
5944 | 983 | 01-jan-06 | 31-dec-15 |
14888 | 983 | 01-jan-16 | 31-dec-28 |
This is the preferred output:
License number | Relation number | Starting date | Ending date | Broken relation |
9809 | 1829 | 01-jan-10 | 31-dec-17 | 0 |
11907 | 1829 | 01-jan-18 | 31-dec-30 | 0 |
10023 | 2509 | 01-jan-12 | 31-dec-30 | 0 |
8338 | 1444 | 01-jan-08 | 31-dec-17 | 1 |
15222 | 3898 | 01-jan-15 | 31-dec-32 | 0 |
5944 | 983 | 01-jan-06 | 31-dec-15 | 0 |
14888 | 983 | 01-jan-16 | 31-dec-28 | 0 |
Thanks in advance.
Steef
@Anonymous Please refer the solution in the below post
Proud to be a PBI Community Champion
@Anonymous Please try this as a "New Column"
BrokenFlag = IF(CALCULATE(COUNTROWS(Test157),FILTER(ALL(Test157),Test157[RelationNumber]=EARLIER(Test157[RelationNumber]) && Test157[EndDate] > TODAY()))>0,0,1)
Proud to be a PBI Community Champion
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |