Last value for ach day in a chart.

[rostromarre]

Hi all,

I have a table with multiples entries the same day (the only difference is the time HH:mm/ss) and i need to graph the last value from each day given a period of time

I have this:

And i need to make a visual that shows the last value (based on the champ date/time ) of each row given a period of time through a filter. Something like this:

Filter Date from 01/08/2018 to 05/08/2018

table : Indicateurs

I am new with POwer BI, so any isdeas i really apprrecite

I've been trying with a Messure that identify the last value for each row but this is not working

Measure =
VAR LatestDate =
    CALCULATE ( MAX ( 'Indicateurs'[Date]); ALL ( 'Indicateurs' ) )
RETURN
    IF ( MIN ( 'Indicateurs'[Date]) = LatestDate;1,0)

Thanks in advance!!!!!

[Greg_Deckler]

Is your Date a real Date/Time column? If so, something like this should work. I expect your issue was how you were using ALL:

Measure =
VAR __id = MAX([Id Concentrateur])
VAR LatestDate =
    MAXX(FILTER(ALL('Indicateurs'),[Id Concentrateur]=__id), 'Indicateurs'[Date])
RETURN
    IF ( MIN ( 'Indicateurs'[Date]) = LatestDate;1,0)

[rostromarre]

thanks Greg.

Your idea is not working (I still don"t know how to fix it) but I think the mistake is with the Var __id, this varibale is picking up only the MAX value of th whole column Id Concentrateur, and actually it should take all the "id Concentratureur"  I have within the period of time chosen (certainly more than one).

I trying to work from your idea, any help i really ppreciate!! thanks in advance

[gooranga1 · Accepted solution]

Hi, @rostromarre is this based on a sql query?

If it is you could  add a new column to the query to give you the last row for an Id Contrentrateur on any particular day.

If it is SQL you could add in the edit quereis and edit source;

ROWNUMBER() OVER (PARTITION BY [Id Concentrateur],CONVERT(DATE,[Date]) ORDER BY [Date] DESC) AS LatestRow

 This be 1 for all the rows you need so you could filter on this field being 1.

[rostromarre]

thanks @gooranga1

I did what you proposed and edited the source. I added the column in the extract query and after I manipulated the chart using the "latestROw" as a filter.

I really wanted to find a solution direclty in Power BI 😞 , but this is working quite good.. thanks a lot

[gooranga1]

Or you could try RankX?

I create a DateOnly column based on your screenshot like;

DateOnly = DATE(YEAR(Table12[Date]),month(Table12[Date]),day(Table12[Date]))

Then used that in  a Rankx column.

Rank = rankx(filter(Table12,Table12[DateOnly] = EARLIER(Table12[Dateonly])),'Table12'[Date],,,dense)

Not sure if that is what you are after though.

[rostromarre]

Hello @Greg_Deckler

I was cheking and actually your idea is working but only if I use "Date" as a filter, if I add "id concentrateur" as part if the filter, the formula doesn't work. 

How could I fix this problem?  i have to apply multiples filters in the rapport, not only date..

thabks in advance.

[rostromarre]

Hello @Greg_Deckler

Thanks for your reply. Yes, my Date is a real date/time column.

I used your formule, but i have the same result :(, it's picking all results for the measure =0,  and  nothing when measure =1

Any ideas of this behavior?

thanks in advance.