Retreiving the latest value from a table

[Anonymous]

Hi!
I am trying to pull out the latest value, substract it from a fixed value in other table, and display the result. I only just started DAX and would appreciate help with the probably very simple task...

Table1 has three basic columns and a couple of thousand rows:

Index  - Unique integer

Time - Recorded time in time format

Value1 - Decimal value

Table2 has a contant value2.

The simple result I am looking for is hence something like LatestTotal = (Latest) Value1 - Value2.

Thanks!

[Anonymous]

Many thanks all for your input!

[Ashish_Mathur]

Hi,

Does this measure work

=MAX(Table1[Value1])-MAX(Table2[Value2])

[amitchandak]

You can create a formula like below to get the values from table 2 to table 1 if there are any filters. Else you can take max from table2 and put in table one. In both case it is a new Column; not measure

New Column = maxx(filter(table2,table2[Col1]= table1[col1] && table2[Col2]= table1[col2]  ),table2[required_col])

You can change the joins as per need. The table need not have these join in the relationship.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[az38 · Accepted solution]

Hi @Anonymous 

is there any relationships between table1 and table2?

anyway, try a measure

LatestTotal = 
var lastDataTbl1 = calculate(max('Table1'[Time]);ALL('Table1'))
var lastDataTbl2 = calculate(max('Table2'[Time]);ALL('Table2'))
RETURN 
calculate(
SUM('Table1'[Value1]); FILTER(ALL('Table1');'Table1'[Time]=lastDataTbl1)
) - 
calculate(
SUM('Table2'[Value2]); FILTER(ALL('Table2');'Table2'[Time]=lastDataTbl2)
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

HI @Anonymous ,

Can you give us some sample data and the expected results of the same?

This will help us to answer your question quickly.

-Tejaswi