Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
v-alq-msft
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
v-alq-msft
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.

Anonymous
Not applicable

Hi Allan,

 

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

Regards

Joao Monteiro

Ashish_Mathur
Super User
Super User

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/
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 .

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/
lbendlin
Super User
Super User

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.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.