Use a dax calculated table as a measure.

[jasemilly]

I have created created a calculated table and it shows the data as I would like it.

Apart from it's not dynamic.  So I wish to turn it into a measure and at the last step return the Growth value for the agent you can only have one Agent Full Name ever selected.

This is an example entry of the calculated table

Agent Full Name,                         Prev ,                              Last,                                       Mid,                Growth 

A                       ,  01/09/2025 00:00:00,   18/09/2025 00:00:00,            09/09/2025 00:00:00,                   0.789

I don't want to do anything with growth apart from display the value.  It will eventually be displayed on area chart as a custom data label between the Prev and Last markers on the report.

The measure I created doesn't return anything.  I thought this was due to the time stamp in the date field even though it was 00:00:00.

So, I connected my dates[date] field to the calculated table and created a table visual filtered to Agent A added the 'dates'date field and the growth field from the calculated table. This worked perfectly fine.

I am trying to use the calculated table logic in a measure and go one step further and filter by agent and when the current date is the same as what [mid] is then  display.

I have broken my dax measure down in the power bi dax edittor and I have one row with the value I want see here, I am not showing the agents name but the field is populated.
  
This shows measue works

This is the measue itself

Agent Growth for Date Test = 

VAR _Agent = SELECTEDVALUE('CallsReviewScore'[Agent Full Name])
VAR _CurrentDate = MAX('Date'[Date])

-- Recreate your calculated table logic dynamically
VAR AgentDates =
    SUMMARIZE(
        'CallsReviewScore',
        'CallsReviewScore'[Agent Full Name],
        "Last", MAX('CallsReviewScore'[Created Date]),
        "Prev",
            CALCULATE(
                MAX('CallsReviewScore'[Created Date]),
                FILTER(
                    'CallsReviewScore',
                    'CallsReviewScore'[Agent Full Name] = EARLIER('CallsReviewScore'[Agent Full Name])
                        && 'CallsReviewScore'[Created Date] <
                            CALCULATE(
                                MAX('CallsReviewScore'[Created Date]),
                                FILTER(
                                    'CallsReviewScore',
                                    'CallsReviewScore'[Agent Full Name] = EARLIER('CallsReviewScore'[Agent Full Name])
                                )
                            )
                )
            )
    )

-- Add Mid and Growth columns
VAR AgentDatesWithMid =
    ADDCOLUMNS(
        AgentDates,
        "Mid",
            VAR Prev = [Prev]
            VAR Last = [Last]
            RETURN
                IF(
                    OR(ISBLANK(Prev), ISBLANK(Last)),
                    BLANK(),
                    Prev + INT(DATEDIFF(Prev, Last, DAY) / 2)
                ),
        "Growth",
            VAR Agent = [Agent Full Name]
            VAR Prev = [Prev]
            VAR Last = [Last]
            VAR PrevScore =
                CALCULATE(
                    AVERAGE('CallsReviewScore'[Score]),
                    FILTER(
                        'CallsReviewScore',
                        'CallsReviewScore'[Agent Full Name] = Agent
                            && 'CallsReviewScore'[Created Date] = Prev
                    )
                )
            VAR LastScore =
                CALCULATE(
                    AVERAGE('CallsReviewScore'[Score]),
                    FILTER(
                        'CallsReviewScore',
                        'CallsReviewScore'[Agent Full Name] = Agent
                            && 'CallsReviewScore'[Created Date] = Last
                    )
                )
            RETURN
                DIVIDE(LastScore - PrevScore, PrevScore)
    )

-- Filter to selected agent and current date
VAR _Filtered =
    FILTER(
        AgentDatesWithMid,
        [Agent Full Name] = _Agent
            && int([Mid]) >= _CurrentDate
            && int([Mid]) <= _CurrentDate + 1
    )

-- Return the Growth
RETURN
MAXX(_Filtered, [Growth])

So I the measure is working to a point as I can see the row when I use the dax edittor, the calculated table works and displays the value I want.

I believe the issue is a date time stamp issue I have tried to address this with the int([mid]) statment and the <= and the >= 
I have placed the measue on a table with the dates'date' as well and placed on a card visual both filteres to the Agent and they return nothing.
But I am stuck at what to do next.

[v-nmadadi-msft]

Hi @jasemilly 

I wanted to check if you had the opportunity to review to share therequested information , so that we could look into it. Please feel free to contact us if you have any further questions.

Thank you.

[v-nmadadi-msft]

Hi @jasemilly ,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Please show the expected outcome based on the sample data you provided.
Thank you

[Kedar_Pande]

Remove the INT conversion and use direct date comparison:

Agent Growth for Date Test =
VAR _Agent = SELECTEDVALUE('CallsReviewScore'[Agent Full Name])
VAR _CurrentDate = MAX('Date'[Date])
VAR AgentDates =
SUMMARIZE(
'CallsReviewScore',
'CallsReviewScore'[Agent Full Name],
"Last", MAX('CallsReviewScore'[Created Date]),
"Prev", CALCULATE(
MAX('CallsReviewScore'[Created Date]),
FILTER(
ALL('CallsReviewScore'),
'CallsReviewScore'[Agent Full Name] = EARLIER('CallsReviewScore'[Agent Full Name]) &&
'CallsReviewScore'[Created Date] < MAX('CallsReviewScore'[Created Date])
)
)
)
VAR AgentDatesWithMid =
ADDCOLUMNS(
AgentDates,
"Mid", [Prev] + INT(DATEDIFF([Prev], [Last], DAY) / 2),
"Growth",
VAR PrevScore = CALCULATE(AVERAGE('CallsReviewScore'[Score]), 'CallsReviewScore'[Created Date] = [Prev])
VAR LastScore = CALCULATE(AVERAGE('CallsReviewScore'[Score]), 'CallsReviewScore'[Created Date] = [Last])
RETURN DIVIDE(LastScore - PrevScore, PrevScore)
)
RETURN
MAXX(
FILTER(
AgentDatesWithMid,
[Agent Full Name] = _Agent &&
[Mid] = _CurrentDate
),
[Growth]
)

If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[jasemilly]

Hi

Thanks for the response, originally the measure didn't include the int() function I only included to try and resolve issue same goes for the greater than and less than check.

I have just tried again, and I still have the issue

[lbendlin]

Instead of a calculated table you can use a variable inside your measure.  The variable can be a table variable.  The only requirement is that the measure itself needs to return a scalar value.

New Measure := 
var a = TOPN(10,'table')
RETURN COUNTROWS(a)

[jasemilly]

Hi

Thanks for replying but I don't understand how to use this in my situation, I am using a calculated table in my measure as I have proven this works, but is static.  I thought it would be easy to filter the calculated table in the measure and return a scalar value.

[lbendlin]

I am using a calculated table in my measure

You can technically do that but it makes no sense. calculated tables are immutable, and measures depend on filter context. That is why I proposed to use table variables instead as they can be dynamic and can work inside measures.