Obtain a value from a table based on the Last day from another table

[Anonymous]

Hi,
I've a schema with 4 tables and one calendar table.
I need to get the value from a table A based on the latest date from the table B.

I've created this metric:

HD lastDay = 

VAR lastDay = LASTDATE(AgentSchedules[Date])
RETURN

CALCULATE(
                   DISTINCTCOUNT('Head Count' [Full Name]),
                   'Head Count'[Date] = lastDay
)

***** This metric gives me all the days and not only the last day
         But if I replace the 'Head Count'[Date] = lastDay 
          for                         'Head Count'[Date] = Date(2020,05,31)  
          it works and I get only the value for the day.

Why?

                        

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on our description, I create data to reproduce your scenario.

Head Count:

AgentSchedules:

There is no relationship between two tables. You may try to create a measure as below.

Result = 
var _lastday = 
CALCULATE(
    LASTDATE(AgentSchedules[Date]),
    ALL(AgentSchedules[Date])
)
return
IF(
    SELECTEDVALUE('Head Count'[Date]) = _lastday,
    CALCULATE(
        DISTINCTCOUNT('Head Count'[Full Name]),
        'Head Count'[Date] = _lastday
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Allan,

You solution works perfectly.
Thank you so much for you help.

Regards

Joao Monteiro

[Ashish_Mathur]

Hi,

Does this work?

HD lastDay = 

VAR lastDay = MAX(AgentSchedules[Date])
RETURN

CALCULATE(
                   DISTINCTCOUNT('Head Count' [Full Name]),
                   'Head Count'[Date] = lastDay
)

[Anonymous]

Thanks for your help but it didn't work.

If I create a Matrix with Line as Date and Values ​​with the metric that you suggested, everydays will be filled, not only the last day .

[Ashish_Mathur]

Hi,

Did you try my solution?  If it does not work, then share the link from where i can download your PBI file and show the problem clearly.

[lbendlin]

your lastDay variable is calculated in the filter context. you need to break out of that if you truly want to find the single last day in your table B. Use CALCULATE, with an ALL() filter thrown in for good measure  (no pun intended). Adjust as needed depending on your data model.