Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to fiind a target date per project

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.


 

projectskeyfiscalPeriodMaxJTDRevenue Per Project**bleep**. Base Forecast AmountTarget Date
129964720200163,523.921,250.41202005
129964720200263,523.926,252.03202005
129964720200363,523.9216,255.28202005
129964720200463,523.9235,011.38202005
129964720200563,523.9262,520.33202005
129964720200663,523.9290,029.27202005
129964720200763,523.92117,538.21202005
129964720200863,523.92136,294.31202005
129964720200963,523.92146,297.56202005
129964720201063,523.92151,299.19202005
129964720201163,523.92152,549.59202005
129964720201263,523.92153,800.00202005
130142020200167,706.521,141.46202005
130142020200267,706.525,707.28202005
130142020200467,706.5226,633.98202005
130142020200567,706.5251,746.02202005
130142020200667,706.5276,858.06202005
130142020200767,706.52101,970.10202005
130142020200867,706.52119,091.94202005
130142020200967,706.52128,223.59202005
130142020201067,706.52132,789.42202005
130142020201167,706.52133,930.87202005
130142020201267,706.52135,072.33202005
130182120200178,405.158,544.00202004
130182120200278,405.1540,584.00202004
130182120200478,405.1574,404.00202004
130182120200578,405.1581,524.00202004
130182120200678,405.1583,660.00202004
130182120200778,405.1585796.00202004
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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