Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello
I've added an example of the table below:
Im trying to populate another table with meter readings closest to to the last day of the month and split by meter. I managed to do this on excel (without filtering by meter) by using the index and min functions.
Does anyone know of a formula that is capable of doing this? The end aim is to get the difference in every meter reading from month to month.
Thanks in advance, Ross
Solved! Go to Solution.
Assume the first table is called Table1 and the second table is Table2, you can add two calculated columns to the second table with below DAX.
A-MAIN =
MAXX (
TOPN (
1,
FILTER ( Table1, Table1[Meter] = "A-MAIN" ),
ABS ( DATEDIFF ( Table2[MonthEnd], Table1[Date], DAY ) ), ASC
),
Table1[Date]
)
B-MAIN =
MAXX (
TOPN (
1,
FILTER ( Table1, Table1[Meter] = "B-MAIN" ),
ABS ( DATEDIFF ( Table2[MonthEnd], Table1[Date], DAY ) ), ASC
),
Table1[Date]
)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Assume the first table is called Table1 and the second table is Table2, you can add two calculated columns to the second table with below DAX.
A-MAIN =
MAXX (
TOPN (
1,
FILTER ( Table1, Table1[Meter] = "A-MAIN" ),
ABS ( DATEDIFF ( Table2[MonthEnd], Table1[Date], DAY ) ), ASC
),
Table1[Date]
)
B-MAIN =
MAXX (
TOPN (
1,
FILTER ( Table1, Table1[Meter] = "B-MAIN" ),
ABS ( DATEDIFF ( Table2[MonthEnd], Table1[Date], DAY ) ), ASC
),
Table1[Date]
)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |