Selecting and summarizing a value in the most recent row dynamically

[jm_n]

Hi!

I haven't been able to find a straightforward answer to this particular challenge, so here it goes.

My data has ids, values (binary) and a date. One id can only have max one row per day.

How do I dynamically select the most recent rows for the ids in relation to my DateKey table and summarize the value column.

E.g. for the reporting date of 03/01/2018, value for x would be 0 and the value for y would be 1. I.e. summarized result=1. The desired end result is a visual showing the summarized result historically for every day.

id	value	date
x	0	01/01/2018
x	1	02/01/2018
x	0	03/01/2018
y	1	02/01/2018
y	0	04/01/2018

Thanks already in advance!

[AlB]

Hi @jm_n

Try this:

1. Place Table1[date] in the rows of a matrix visual

2. Create this measure and place it in values of the matrix:

Measure = 
VAR _SelectedDate = SELECTEDVALUE ( Table1[date] )
RETURN
    CALCULATE (
        SUMX (
            FILTER (
                Table1;
                Table1[date]
                    = CALCULATE (
                        MAX ( Table1[date] );
                        ALLEXCEPT ( Table1; Table1[id] );
                        FILTER ( ALL ( Table1[date] ); Table1[date] <= _SelectedDate )
                    )
            );
            Table1[value]
        );
        ALL ( Table1 )
    )

[jm_n · Accepted solution]

Hi! 

Thanks for the help. For some reason this didn't work. I ended up doing everything in SQL before taking the data to PowerBI. Basically:

1. join all rows with a date table that contains reporting dates for the past year
2. find and keep the row that is closest to the reporting date
3. summarize values by reporting date

It is a bit heavy query but it works.