cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mab Frequent Visitor
Frequent Visitor

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?

max date.JPG

 

3 REPLIES 3
Community Support Team
Community Support Team

Re: Max date for each day

Hi @Mab

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

 

Mab Frequent Visitor
Frequent Visitor

Re: Max date for each day

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

 

Ingestion.JPGStep 1max date.JPGstep 3pivot.JPGstep 2

 

Community Support Team
Community Support Team

Re: Max date for each day

Hi @Mab

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 

11.png  

 

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