Forum Discussion
Lookup the Quarter value from another table
I have two tables, Snapshot Table and Close Date table. I want to pull the Snapshot data, its arr and then for the same quarter, all the matching values of arr in close date table.
Please help me in this!
| Input | ||||||
| Snapshot_date | Account_id | Arr | Close_Date | Account_id | Arr | |
| Mar-20 | 12 | 10 | Mar-20 | 12 | 8 | |
| Mar-20 | 34 | 2 | Apr-20 | 34 | 5 | |
| May-20 | 15 | 7 | May-20 | 15 | 3 | |
| May-20 | 16 | 9 | Jun-20 | 16 | 2 | |
| Aug-20 | 11 | 7 | Aug-20 | 11 | 1 | |
| Aug-20 | 10 | 7 | Sep-20 | 10 | 13 | |
| Nov-20 | 14 | 7 | Nov-20 | 14 | 7 | |
| Nov-20 | 13 | 7 | Nov-20 | 13 | 44 | |
| Output | ||||||
| Month | snapshot_arr | closed_arr | ||||
| Mar-20 | 12 | 8 | ||||
| May-20 | 16 | 3 | ||||
| Aug-20 | 14 | 1 | ||||
| Nov-20 | 14 | 51 |
3 Replies
FreemanZSuper User
you may create a calculated table like this:
Table = VAR _table1 = SELECTCOLUMNS( INTERSECT(VALUES(Close_Date[Close_Date]), VALUES(Snapshot[Snapshot_date])), "Date", Close_Date[Close_Date] ) RETURN ADDCOLUMNS( SUMMARIZE(_table1, [Date]), "snapshot_arr", VAR _date = [Date] RETURN SUMX( FILTER( ALL(Snapshot), Snapshot[Snapshot_date] = _date ), Snapshot[Arr] ), "closedate_arr", VAR _date = [Date] RETURN SUMX( FILTER( ALL(Close_Date), Close_Date[Close_date] = _date ), Close_Date[Arr] ) )verified with your sample data and worked like this:
- Ashish_Mathur
Hi,
How you arrived at the numbers in the snapshot_arr column of the output table? Give a proper explanation.
