Calculated Column to Measure

[AllanBerces]

Hi can anyone help me to change to measure my calculated column in TRS Table, column (Overall_Cum_PT_YTD) 

Overall_Cum_PT_YTD = VAR CurrentDate = 'TRS'[WeekNo:]
VAR Filteredtable = FILTER('TRS','TRS'[WeekNo:]<=CurrentDate )

return

CALCULATE(SUM('TRS'[NPTHrs]), TRS[Type] <> "Non-productive", Filteredtable)

https://drive.google.com/file/d/1i1JojzfjyY7Mp4304xyBoNOKvuQJ595F/view?usp=sharing

Thank you

[quantumudit]

Hello @AllanBerces 
Please use the following DAX to create the measure with the same logic that you have used for creating the calculated column.

Overall_Cum_PT_YTD_Measure = 
VAR CurrentDate = SELECTEDVALUE(TRS[WeekNo:])
VAR Filteredtable = FILTER(
    ALL('TRS'),
    TRS[WeekNo:] <= CurrentDate 
    && TRS[Type]  <> "Non-productive"
)

RETURN
CALCULATE(
    SUM('TRS'[NPTHrs]), 
    Filteredtable
)

Additionally, there is a BSPTA calculated column in your TRS that is erroneous. Make sure to fix it or delete it, as it might create issues testing other aspects.

I am also attaching the Power BI file for your reference.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Proud to be a Super User

[AllanBerces]

Hi @quantumudit my mistake ignore the first DIRED OUTPUT, my DESIRED OUTPUT

Thank you

[AllanBerces]

Hi @quantumudit 

Desired Output

[AllanBerces]

Hi @quantumudit thank yu very much for the reply, but when i put table show same value for used_AREA, possible to segregate by used_AREA

Thank you

[quantumudit · Accepted solution]

Hello @AllanBerces 

To make the formula work when the [used_Area] column is in context, you can use the following DAX instead:

Overall_Cum_PT_YTD_Measure = 
VAR CurrentDate =
    SELECTEDVALUE ( TRS[WeekNo:] )
VAR Filteredtable =
    FILTER (
        ALL ( 'TRS' ),
        TRS[WeekNo:] <= CurrentDate
            && TRS[Type] <> "Non-productive"
    )
RETURN
    IF (
        ISINSCOPE ( TRS[used_AREA] ),
        CALCULATE (
            SUM ( 'TRS'[NPTHrs] ),
            Filteredtable,
            VALUES ( TRS[used_AREA] )
        ),
        CALCULATE (
            SUM ( 'TRS'[NPTHrs] ),
            Filteredtable
        )
    )

I am attaching the Power BI with modified formula in the attachment.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Proud to be a Super User

[AllanBerces]

Hi @quantumudit thank you very much working perfectly

[quantumudit]

@AllanBerces 

Appreciate your Kudo 👍

Proud to be a Super User