FirstSortedValue gives either Nulls or same value for all categories

[devarshigoswami]

Thanks for taking the time to look at this! 🙂
I have a fact table that looks like this 

Category      date                    value

x                       22/1/2022         200

x                       21/1/2022          100

y                       22/1/2022         120  

y                      21/1/2022         115. 

For each category, I need the maximum date value and corresponding for each maximum date

So I did this ,

`````

Dimension: category

Measure1 : max(date)

Measure2: firstSortedValue(value,-date)

`````

this gives out all null values for Measure2

if I try firstSortedValue(distinct value,-date) I get the same value across all categories which is not possible. 

Expected table should be :

category       Max(date)       firstSortedValue(value,-date)

x                       22/1/2022         200

y                       22/1/2022         120  

How can I achieve this? 

[BrunPierre]

@devarshigoswami See the attached.

[sidhiq91]

@devarshigoswami  Just one correction. Set your Date to the right format and rest everything looks fine. Please see below.

NoConcatenate
Temp:
Load Category,
value,
Date(Date#(date,'DD/MM/YYYY'),'DD/MM/YYYY') as Date

Inline [
Category ,date,value

x,22/1/2022,200

x,21/1/2022,100

y,22/1/2022,120

y,21/1/2022,115
];

Exit Script;

Measure1: max(Date)

Measure2 :FirstSortedValue(value,-Date)

[BrunPierre]

@devarshigoswami Did it solve your problem? please mark accordingly.