Max date for each day

[Anonymous]

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?

[v-juanli-msft]

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

[Anonymous]

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

[v-juanli-msft]

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