Help With Complex Measure

[AlexDawson111]

Hi,

I am trying to recreate a formula field that is currently used in my Crystal Reports and turn it into a measure in Power BI.

The formula field in Crystal is currently:-

if {urvPMContractAnalysisDD_WithPCOEstCost.Mth} < {?CurrentPeriodStart} then
if {urvPMContractAnalysisDD_WithPCOEstCost.ProjMth} < {?CurrentPeriodStart} then
{urvPMContractAnalysisDD_WithPCOEstCost.ProjDollars}
else
{urvPMContractAnalysisDD_WithPCOEstCost.ContractAmt}
else
0

I currently have a Dates Table which has no relationships to my Fact table. 

My slicer is YEAR/MONTH is replacing the CurrentPeriodStart in the Crystal formula.

Within my fact table i want to apply the measure at row level on each job record.  Below is a sample of the data:-

Would apprechiate any help on how about i would re construct this as its causing me a few headaches now.

Many Thanks

Alex

[v-yalanwu-msft]

Hi, @AlexDawson111 ;

Is your problem solved? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @AlexDawson111 ;

Try it.

measure =
IF (
    MAX ( [urvPMContractAnalysisDD_WithPCOEstCost.Mth] )
        < MAX ( [?CurrentPeriodStart] ),
    IF (
        MAX ( [urvPMContractAnalysisDD_WithPCOEstCost.ProjMth] )
            < MAX ( [?CurrentPeriodStart] ),
        MAX ( urvPMContractAnalysisDD_WithPCOEstCost.ProjDollars ),
        MAX ( urvPMContractAnalysisDD_WithPCOEstCost.ContractAmt )
    ),
    0
)

If not right,Can you post sample data as text and expected output?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.