Forum Discussion
Retreiving the latest value from a table
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!
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
5 Replies
- az38
Community Champion
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
- amitchandak
Super User
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-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601 - Ashish_Mathur
Hi,
Does this measure work
=MAX(Table1[Value1])-MAX(Table2[Value2])
