Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Here's a summary of my table. For the purpose, let's call it Table1
The first column is the projectskey, an unique number which is different for each project
Then we have the MaxJTDRevenuePerProject which is a measure and is already calculated and know using variables.
I would like to know how I can find the target Date per project. This date correspond to the last.date where the **bleep**. BaseForecastAmount is less or equal to the MaxJTDRevenuePerProject.
projectskey | fiscalPeriod | MaxJTDRevenue Per Project | **bleep**. Base Forecast Amount | Target Date |
1299647 | 202001 | 63,523.92 | 1,250.41 | 202005 |
1299647 | 202002 | 63,523.92 | 6,252.03 | 202005 |
1299647 | 202003 | 63,523.92 | 16,255.28 | 202005 |
1299647 | 202004 | 63,523.92 | 35,011.38 | 202005 |
1299647 | 202005 | 63,523.92 | 62,520.33 | 202005 |
1299647 | 202006 | 63,523.92 | 90,029.27 | 202005 |
1299647 | 202007 | 63,523.92 | 117,538.21 | 202005 |
1299647 | 202008 | 63,523.92 | 136,294.31 | 202005 |
1299647 | 202009 | 63,523.92 | 146,297.56 | 202005 |
1299647 | 202010 | 63,523.92 | 151,299.19 | 202005 |
1299647 | 202011 | 63,523.92 | 152,549.59 | 202005 |
1299647 | 202012 | 63,523.92 | 153,800.00 | 202005 |
1301420 | 202001 | 67,706.52 | 1,141.46 | 202005 |
1301420 | 202002 | 67,706.52 | 5,707.28 | 202005 |
1301420 | 202004 | 67,706.52 | 26,633.98 | 202005 |
1301420 | 202005 | 67,706.52 | 51,746.02 | 202005 |
1301420 | 202006 | 67,706.52 | 76,858.06 | 202005 |
1301420 | 202007 | 67,706.52 | 101,970.10 | 202005 |
1301420 | 202008 | 67,706.52 | 119,091.94 | 202005 |
1301420 | 202009 | 67,706.52 | 128,223.59 | 202005 |
1301420 | 202010 | 67,706.52 | 132,789.42 | 202005 |
1301420 | 202011 | 67,706.52 | 133,930.87 | 202005 |
1301420 | 202012 | 67,706.52 | 135,072.33 | 202005 |
1301821 | 202001 | 78,405.15 | 8,544.00 | 202004 |
1301821 | 202002 | 78,405.15 | 40,584.00 | 202004 |
1301821 | 202004 | 78,405.15 | 74,404.00 | 202004 |
1301821 | 202005 | 78,405.15 | 81,524.00 | 202004 |
1301821 | 202006 | 78,405.15 | 83,660.00 | 202004 |
1301821 | 202007 | 78,405.15 | 85796.00 | 202004 |
Solved! Go to Solution.
Hi @Anonymous,
You can use following calculate column formula to get the date that meets to the target:
Target =
CALCULATE (
MAX ( 'Table'[fiscalPeriod] ),
FILTER (
ALLSELECTED ( 'Table' ),
[projectskey] = EARLIER ( 'Table'[projectskey] )
&& [**bleep**. Base Forecast Amount] <= EARLIER ( [MaxJTDRevenue Per Project] )
)
)
Regards,
Xiaoxin Sheng
Hi,
I assume you want a measure for this (not a calculated column formula). Share the link from where i can download your PBI file.
Hi @Anonymous,
You can use following calculate column formula to get the date that meets to the target:
Target =
CALCULATE (
MAX ( 'Table'[fiscalPeriod] ),
FILTER (
ALLSELECTED ( 'Table' ),
[projectskey] = EARLIER ( 'Table'[projectskey] )
&& [**bleep**. Base Forecast Amount] <= EARLIER ( [MaxJTDRevenue Per Project] )
)
)
Regards,
Xiaoxin Sheng
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |