Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need calculate last year meter reading data, for example, Item No: EQ10271, 2013 begin of year is 88, end of year is: 210, so this year, YTD Meter is : 210-88. but for 2014, begin of year is 210, end of year is 1220, I need 1220-210. now, I cannot find out "210" for 2014. how to get this data, thank you very much
Solved! Go to Solution.
Hi @Anonymous,
You can try to use following calculated column formula if it works for your scenario.(it will lookup previous data if first date same as last date)
Diff = VAR list = CALCULATETABLE ( VALUES ( Table[Count.Posting_Date] ), FILTER ( ALL ( Table ), [Service_Item_No] = EARLIER ( Table[Service_Item_No] ) && [Posting_Date-Copy] = EARLIER ( Table[Posting_Date-Copy] ) ) ) VAR first = MINX ( list, [Count.Posting_Date] ) VAR last = MAXX ( list, [Count.Posting_Date] ) VAR previous = IF ( first = last, MAXX ( FILTER ( ALL ( Table ), [Service_Item_No] = EARLIER ( Table[Service_Item_No] ) && [Posting_Date-Copy] = EARLIER ( Table[Posting_Date-Copy] ) && [Count.Posting_Date] < last ), [Count.Posting_Date] ), first ) RETURN LOOKUPVALUE ( Table[Count.Reading], Table[Service_Item_No], [Service_Item_No], Table[Count.Posting_Date], last ) - LOOKUPVALUE ( Table[Count.Reading], Table[Service_Item_No], [Service_Item_No], Table[Count.Posting_Date], previous )
Regards,
Xiaoxin Sheng
Hi @Anonymous,
You can try to use following calculated column formula if it works for your scenario.(it will lookup previous data if first date same as last date)
Diff = VAR list = CALCULATETABLE ( VALUES ( Table[Count.Posting_Date] ), FILTER ( ALL ( Table ), [Service_Item_No] = EARLIER ( Table[Service_Item_No] ) && [Posting_Date-Copy] = EARLIER ( Table[Posting_Date-Copy] ) ) ) VAR first = MINX ( list, [Count.Posting_Date] ) VAR last = MAXX ( list, [Count.Posting_Date] ) VAR previous = IF ( first = last, MAXX ( FILTER ( ALL ( Table ), [Service_Item_No] = EARLIER ( Table[Service_Item_No] ) && [Posting_Date-Copy] = EARLIER ( Table[Posting_Date-Copy] ) && [Count.Posting_Date] < last ), [Count.Posting_Date] ), first ) RETURN LOOKUPVALUE ( Table[Count.Reading], Table[Service_Item_No], [Service_Item_No], Table[Count.Posting_Date], last ) - LOOKUPVALUE ( Table[Count.Reading], Table[Service_Item_No], [Service_Item_No], Table[Count.Posting_Date], previous )
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |