Forum Discussion
Max date for each day
I have a table that has there columns that I will be using:-
Table_name
rec_eff_dt Date that table was loaded
Valid date If rec_eff_dt <= Today then 1 else 0
Unique key Table_name&rec_eff_dt&Valid date ----> abc12/10/20181 (This is what the unique key looks like)
I have created another pivot table
Unique key
Max(rec_eff_dt)
3rd table
Date
I want to show in a table:-
for every table name on a particular day (we are using last 7 days from a separate date table as column headers) what was the max(rec_eff_dt)
In excel they are performing and index match where they choose the table name, the date from column header and 1 (i.e table name& A1(say its 1/7/19)&"1" to create a custom unique key with the column header date and looking this value up in pivot table to find the corresponding max(rec_eff_dt) and populating that in the matrix.
How can I accomplish this in PowerBI. How do I perform a dynamic lookup by concatenating the table_name, column header date and 1?
3 Replies
Hi Anonymous
I don't understand what you want finally.
How does this look like:
another pivot table
Unique key
Max(rec_eff_dt)
Could you show an example of the output as the following said,
"for every table name on a particular day (we are using last 7 days from a separate date table as column headers) what was the max(rec_eff_dt)"
the screenshot make me puzzled.
Best Regards
Maggie
Hi Maggie,
These reports are built out in excel and I am trying to replicate these on PowerBI.
What excel has is:-
sheet Ingestion data:- which has data for multiple days for tables and when they were refreshed I am attching screenshots:-
step 1 - Ingestion data (table_name, rec_eff_dt, rec_create_dt)
step 2 Pivot ( concatenate(table_name,rec_eff_dt) --> Unique key , max(rec_eff_dt) step
3 Index match
In powerBI I
have the data - step 1
I created a group by - step 2 (something like pivot)
I have another table with just dates in it --> Date_list
Now for the step 3 index match I want the max(rec_eff_dt) for Date_list dates (last 7 days data) so the lookup I will be permorming is:- concatinate(Table_name, date_list) and look this value up in concatenate(Table_name,rec_eff_dt) (Pivot table) and list the corresponding max(rec_eff_dt) value
Step 1step 3step 2
Hi Anonymous
I make a test but i am stuck with some puzzle.
test with table you show in step1,
then group by, i get this
original table
table name rec rdl a 12/7/2018 12/19/2018 a 12/8/2018 12/19/2018 a 12/9/2018 12/19/2018 a 12/10/2018 12/19/2018 a 12/11/2018 12/19/2018 a 12/12/2018 12/19/2018 a 12/13/2018 12/19/2018 a 12/14/2018 12/19/2018 a 12/15/2018 12/19/2018 a 12/16/2018 12/19/2018 b 12/9/2018 12/19/2018 b 12/10/2018 12/19/2018 b 12/11/2018 12/19/2018 b 12/12/2018 12/19/2018 b 12/13/2018 12/19/2018 b 12/14/2018 12/19/2018 b 12/15/2018 12/19/2018 b 12/16/2018 12/19/2018 -> after group by
then i have a date table which just has a date column,
next i'm stucked, how do i connect the two tables?
in your step3 picture, "1/7/19~1/14/19" is from "date" table, dates in columns are max(rec_eff_dt) , right?
Best Regards
Maggie
