cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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

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?

                        




1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Hi, @Anonymous 

 

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

Head Count:

e1.png

 

AgentSchedules:

e2.png

 

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:

e3.png

 

Best Regards

Allan

 

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

View solution in original post

6 REPLIES 6
Highlighted
Super User II
Super User II

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.

 

 

Highlighted
Super User III
Super User III

Hi,

Does this work?

HD lastDay = 

VAR lastDay = MAX(AgentSchedules[Date])
RETURN

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Anonymous
Not applicable

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 .

Highlighted

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Community Support
Community Support

Hi, @Anonymous 

 

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

Head Count:

e1.png

 

AgentSchedules:

e2.png

 

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:

e3.png

 

Best Regards

Allan

 

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

View solution in original post

Highlighted
Anonymous
Not applicable

Hi Allan,

 

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

Regards

Joao Monteiro

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors