Calculated Column to Measure

[AllanBerces]

Hi good day can anyone help me convert my calculated column into measure. I m having trouble on my report. Thank you in advance

%_Daily Plan = DIVIDE('Table01'[Overall_Cum_Plan_Hrs],'Table01'[Overall_Total_Column])

%_Actual = IF(ISBLANK(DIVIDE('Table01'[**bleep** Earned],'Table01'[Overall_Total_Column])),0, DIVIDE('Table01'[**bleep** Earned],'Table01'[Overall_Total_Column]))

Daily_Earned =
CALCULATE (
SUM ( 'TABLE PROGRESS'[Daily Earned] ),
FILTER (
ALL ( 'TABLE PROGRESS' ),
'TABLE PROGRESS'[Progress Date] = EARLIER ( 'Table 01'[PlanDate] )
&& 'TABLE PROGRESS'[Complex] = EARLIER ( 'Table 01'[Complex] )
&& 'TABLE PROGRESS'[Mode] = EARLIER ( 'Table 01'[Mode] )
)
)

Daily_Earned w/ Negative =
VAR Result =
CALCULATE (
SUM ( 'TABLE PROGRESS'[Daily Earned] ),
FILTER (
ALL ( 'TABLE PROGRESS' ),
'TABLE PROGRESS'[Progress Date] = EARLIER ( 'Table 01'[PlanDate] )
&& 'TABLE PROGRESS'[Complex] = EARLIER ( 'Table 01'[Complex] )
&& 'TABLE PROGRESS'[Mode] = EARLIER ( 'Table 01'[Mode] )

)
)
RETURN
IF ( Result < 0, 0, Result )

**bleep** Earned =
IF([PlanDate] <= TODAY(),
CALCULATE(
SUM('Table 01'[Daily_Earned]),
FILTER(
ALL('Table 01'),
'Table 01'[Complex] = EARLIER('Table 01'[Complex]) &&
'Table 01'[Mode] = EARLIER('Table 01'[Mode]) &&
'Table 01'[PlanDate] <= EARLIER('Table 01'[PlanDate]) &&
[PlanDate] <= TODAY()
)
)
)

Remaining = CALCULATE (
SUMX('MAIN TABLE', 'MAIN TABLE'[Remaining hours]),
FILTER (
ALL ( 'MAIN TABLE' ),
'MAIN TABLE'[Complex] = EARLIER ( 'Table 01'[Complex])&&
'MAIN TABLE'[Mode] = EARLIER ( 'Table 01'[Mode])
)
)

Overall_Total_Column =
CALCULATE (
SUM('Table 01'[Plan Hrs]),
FILTER (
ALL ( 'Table 01' ),
'Table 01'[Complex] = EARLIER('Table 01'[Complex]) &&
'Table 01'[Mode] = EARLIER('Table 01'[Mode])
)
)

Overall_Cum_Plan_Hrs = CALCULATE(
SUM('Table 01'[Plan Hrs]),
FILTER(
ALL('Table 01'),
'Table 01'[Complex] = EARLIER('Table 01'[Complex]) &&
'Table 01'[Mode] = EARLIER('Table 01'[Mode]) &&
'Table 01'[PlanDate] <= EARLIER('Table 01'[PlanDate])
)
)

[v-prasare]

Hi @AllanBerces,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@Balqis_Adan & @krishnakanth240 ,Thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-prasare]

Hi @AllanBerces,

As @krishnakanth240  requested can you share pbix files. so, that this will allow the community to better understand your data model, relationships, and expected output, and provide a more accurate solution.

Thanks,

Prashanth

[krishnakanth240]

Hi @AllanBerces 

Can you try these measures 

Daily Earned =
CALCULATE(SUM('TABLE PROGRESS'[Daily Earned]),'TABLE PROGRESS'[Progress Date] = MAX('Table 01'[PlanDate]),'TABLE PROGRESS'[Complex] = SELECTEDVALUE('Table 01'[Complex]),'TABLE PROGRESS'[Mode] = SELECTEDVALUE('Table 01'[Mode]))

Daily Earned (Neg) =
VAR Result = CALCULATE(SUM('TABLE PROGRESS'[Daily Earned]),'TABLE PROGRESS'[Progress Date] = MAX('Table 01'[PlanDate]),'TABLE PROGRESS'[Complex] = SELECTEDVALUE('Table 01'[Complex]),'TABLE PROGRESS'[Mode] = SELECTEDVALUE('Table 01'[Mode]))
RETURN
IF(Result < 0, 0, Result)

Cumulative Earned =
VAR CurrDate = MAX('Table 01'[PlanDate])
RETURN
IF(CurrDate <= TODAY(), CALCULATE(SUMX(
VALUES('Table 01'[PlanDate]),[Daily Earned]),
'Table 01'[PlanDate] <= CurrDate,'Table 01'[Complex] = SELECTEDVALUE('Table 01'[Complex]),'Table 01'[Mode] = SELECTEDVALUE('Table 01'[Mode])))

Overall Total =
CALCULATE(SUM('Table 01'[Plan Hrs]),
ALLEXCEPT('Table 01', 'Table 01'[Complex], 'Table 01'[Mode]))

Cumulative Plan Hrs =
VAR CurrDate = MAX('Table 01'[PlanDate])
RETURN
CALCULATE(SUM('Table 01'[Plan Hrs]),'Table 01'[PlanDate] <= CurrDate, ALLEXCEPT('Table 01', 'Table 01'[Complex], 'Table 01'[Mode]))

Remaining =
CALCULATE(SUM('MAIN TABLE'[Remaining hours]),
ALLEXCEPT('MAIN TABLE', 'MAIN TABLE'[Complex], 'MAIN TABLE'[Mode]))

% Daily Plan =
DIVIDE([Cumulative Plan Hrs], [Overall Total])
% Actual =
DIVIDE([Cumulative Earned], [Overall Total], 0)

[Balqis_Adan]

the main challenge here is replacing the EARLIER function with Variables (VAR) to capture the current context of the row. Also, ensure you use MAX or SELECTEDVALUE to identify the current Date, Complex, and Mode within your Measure. This will make your report much faster than using Calculated Columns

Cumulative Plan Hrs

Overall_Cum_Plan_Hrs_Measure =
VAR CurrentDate = MAX('Table 01'[PlanDate])
VAR CurrentComplex = SELECTEDVALUE('Table 01'[Complex])
VAR CurrentMode = SELECTEDVALUE('Table 01'[Mode])

RETURN
CALCULATE(
SUM('Table 01'[Plan Hrs]),
FILTER(
ALL('Table 01'),
'Table 01'[Complex] = CurrentComplex &&
'Table 01'[Mode] = CurrentMode &&
'Table 01'[PlanDate] <= CurrentDate
)
)

Overall Total



Overall_Total_Measure =
VAR CurrentComplex = SELECTEDVALUE('Table 01'[Complex])
VAR CurrentMode = SELECTEDVALUE('Table 01'[Mode])

RETURN
CALCULATE (
SUM('Table 01'[Plan Hrs]),
FILTER (
ALL ( 'Table 01' ),
'Table 01'[Complex] = CurrentComplex &&
'Table 01'[Mode] = CurrentMode
)
)

%_Daily Plan



%_Daily Plan_Measure =
DIVIDE(
[Overall_Cum_Plan_Hrs_Measure],
[Overall_Total_Measure],
0
)

[AllanBerces]

Hi @krishnakanth240 thank you for the reply,but for all the measure you provided only this measure give correct data. Daily Earned and Daily Earned (Neg). The rest are incorect

[krishnakanth240]

Okay can you provide the logic for the other measures that you need

[AllanBerces]

@krishnakanth240 thank you very much for the reply, below is my logic. Table 01 and Main Table are connected to bridge table complex and Mode,. Table 01 and Table Progress are connected to Calendar Table

Thank you

[krishnakanth240]

Okay, can you share pbix file link over a onedrive with providing access to it.

1.Can you explain in more detail the requirement with respect to Data Modelling.

2.From requirements part, what are the logics you need as output from the measures