How to : Get last value of date by category

[Sebllo]

Hello, 

I'm realy stuck and I have spent long time on this point... 

I would like to know how can I get the last value of a day for a category. 

Let's take this example : 

As you can see we have 3 categories : A, B and C and we have 3 dates but 2 days. Two datetime are on 14.01.2021 and one datetime on 15.01.2021. 

So I would like to calculate the column LastByCat with a measure that will repeat the last value for a category by day as shown in my screen. 

Can you please help me on this. 

Thx all. 

Best

[Anonymous]

Hi Buddy, maybe it can help you.  It is a simple solution using TOP N filters by Date.

https://www.youtube.com/watch?v=q1GE9orj7Qk

best!

[PaulDBrown · Accepted solution]

@Sebllo 

Here is one way, though it involves a number of steps:

1) Create a new column for the date (and set the field type to Date):

 2) create measure for the sum of the scores:

Sum Score = SUM(FactTable[Score])

3) Create a measure to find the max value by date:

Max Score by Cat and date = 
VAR seldate = SELECTEDVALUE(FactTable[Date])
VAR MaxValue = MAXX(FILTER(ALLEXCEPT(FactTable, FactTable[Cat]), 
                FactTable[Date] = seldate), 
                [DateTime])
RETURN
CALCULATE([Sum Score], 
        FILTER(FactTable, FactTable[DateTime] = MaxValue))

 4) the final measure to return the [Max Score by Cat and date] for each category:

Final Measure= 
VAR seldate = SELECTEDVALUE(FactTable[Date])
RETURN
CALCULATE([Max Score by Cat and date], 
            FILTER(ALLEXCEPT(FactTable, FactTable[Cat]), 
                FactTable[Date] = seldate))

Which gets you this:

[Sebllo]

Thank a lot @PaulDBrown 

It's working perfectly! 

The only point, maybe it's a stupid question is that I cannot use the Final Measure in a Chart... This measure works properly on a table but when I try to use it as value on a chart, it stay empty. 

I have checked the Data Type of the Measure and it's correctly set to Whole Number... 

Any idea ?? Thx again!

[PaulDBrown]

@Sebllo 

Seeing as the measure calculates based on date and date/time fields, you probably need to a chart which has these in the axis. Something along these lines, though it depends what you are trying to show 

[Sebllo]

@PaulDBrown  You're right, when I'm adding the date on Axis the measure is take in consideration and I can see data on chart. But in my case, I need to create a simple Radar Chart where the Category is my column Category and Y axis (values) is the Average of my Final Measure.... 

Something like that : 

[PaulDBrown]

@Sebllo 

Can you define what you mean by the average of the final measure? The average of dates? the average of date/times? if so, which dates or date/times (since the Radar chart doesn't have any dates as a filter context)?

[Sebllo]

I'm sorry and thanks to your reply I understood my mistake. 

Now, I've just added a slicer filter based on date because the chart can work for a single day only. And now it works perfectly. 

The Radar Chart must display the last score by cat for each day. Now it's ok !"

Thx again !!! 

[Sebllo]

Sorry to insist, but does anyone have an idea? I am really stuck to finish my report ... Thank you !!

@amitchandak  ? 

[amitchandak]

@Sebllo , Try a new measure like

calculate(lastnonblankvalue(Table[date], max(Table[Score])), allexcept(Table, Table[Cat]))

[Sebllo]

@amitchandak  thanks for your reply.

This measure seems to calculate the max date of my dataset. But what I need is the calculation of the column LastByCat of my example above. 

For each single day, I would like to get the score value of the last DateTime for a category and repeat it for each DateTime of the same day. 

As you can see on my example for the day 14.01.2021 we have two datetime. One at 14:17 and another one at 18h24. I want to get the Cat values of 18h24 (the last datetime) and put it for corresponding cat of 14h17 group. And this day by day. For 15.01.2021 I have only one datetime, so it takes these values. 

Is it more clear ? Thanks again for your precious help !