Displaying a Value in Card Visual Based on Date Chosen

[Ahmedelshark]

Hello all,

I am currently working on a report with two visuals - a Card Visual displaying the 'L Value' and a Date Range Slicer Visual for selecting date range. 

I am trying to get the Card Visual to display the latest 'L Value' based on the date range chosen - see data extract below. For example, if date range chosen is 10 Nov (start) - 12 Nov (end), 'L Value in the card visual should be 0.36. If the date range chosen is 12 Nov (start) - 15 Nov (end), 'L Value' should be 0.45.

Every time I've tried doing it, I get a choice of displaying the sum / average / min / max / count / etc. of the 'L Value' column. All I want is the last corresponding 'L Value' for the end date chosen.

Any help would be appreciated. Thanks guys!

Title	Date	Hours	L Value
Partner	10-Nov-19	0.5	0.00
Associate	12-Nov-19	1.2	2.40
Partner	12-Nov-19	2	0.48
Partner	12-Nov-19	0.8	0.36
Partner	13-Nov-19	1	0.28
Associate	14-Nov-19	3	1.80
Associate	15-Nov-19	3.5	1.09
Partner	15-Nov-19	1	0.45

[BA_Pete · Accepted solution]

Hi @Ahmedelshark ,

Yes, it's doable, but you need to do a quick addition in Power Query first: you need to add an index column to your table.

In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this.

// Paste this in into Advanced Editor
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc8xCoAwDAXQu2TWkKat1NELiHvpIOLgoqDi+dU4tFRx+XR45P96D12/7vO4QgGKynY5SlVfb0IrSQSh8NBs2zJM/T7ejCNTyFcymocltxLEcsm4H0LoJHWVI52UCWH3XmQi0rLKfcy2CZLfKaQ6r7N5nbEQwgk=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t, Date = _t, Hours = _t, #"L Value" = _t]),
    addIndexFromOne = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    chgAllTypes = Table.TransformColumnTypes(addIndexFromOne,{{"Title", type text}, {"Date", type date}, {"Hours", type number}, {"L Value", type number}})
in
    chgAllTypes

You need to make sure you add the index column AFTER you've done your filtering and sorting on the table.

You would then create a measure like this:

_latestL = 
CALCULATE(
    MAX(AhmedesTable[L Value]),
    FILTER(
        AhmedesTable,
        AhmedesTable[Index] = MAX(AhmedesTable[Index])
    )
)

Which should give you the following ouput:

It's worth noting from this output that the table visual does not keep the original sort order unless you use the [Index] field in it and sort on it. If you look at the output example, you can see that the visual shows 0.48 as the last row value, but the measure correctly calculates it as 0.36. Be careful not to confuse your end users with this.

Pete

[BA_Pete]

Hi @Ahmedelshark ,

Do you have a time field in your table?

It makes it a bit more complicated to resolve the latest entry when working with multiple entries on the same date.

Pete

[Ahmedelshark]

Hello @BA_Pete ,

Thanks for replying. 

I do not have a time field. I was wondering whether it could be based on order in which the data has been inputted? So in the data extract provided, the last 'L value' for 12-Nov should be 0.36 (as it is the last row of data for 12 Nov).

[BA_Pete · Accepted solution]

Hi @Ahmedelshark ,

Yes, it's doable, but you need to do a quick addition in Power Query first: you need to add an index column to your table.

In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this.

// Paste this in into Advanced Editor
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc8xCoAwDAXQu2TWkKat1NELiHvpIOLgoqDi+dU4tFRx+XR45P96D12/7vO4QgGKynY5SlVfb0IrSQSh8NBs2zJM/T7ejCNTyFcymocltxLEcsm4H0LoJHWVI52UCWH3XmQi0rLKfcy2CZLfKaQ6r7N5nbEQwgk=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t, Date = _t, Hours = _t, #"L Value" = _t]),
    addIndexFromOne = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    chgAllTypes = Table.TransformColumnTypes(addIndexFromOne,{{"Title", type text}, {"Date", type date}, {"Hours", type number}, {"L Value", type number}})
in
    chgAllTypes

You need to make sure you add the index column AFTER you've done your filtering and sorting on the table.

You would then create a measure like this:

_latestL = 
CALCULATE(
    MAX(AhmedesTable[L Value]),
    FILTER(
        AhmedesTable,
        AhmedesTable[Index] = MAX(AhmedesTable[Index])
    )
)

Which should give you the following ouput:

It's worth noting from this output that the table visual does not keep the original sort order unless you use the [Index] field in it and sort on it. If you look at the output example, you can see that the visual shows 0.48 as the last row value, but the measure correctly calculates it as 0.36. Be careful not to confuse your end users with this.

Pete

[Ahmedelshark]

Thank you very much @BA_Pete . In pasting the code, I get the following error below:

[BA_Pete · Accepted solution]

Hi @Ahmedelshark ,

Try removing the ', Int64.Type' argument from this line.

*EDIT* Ignore the above, easier if you just do it directly on your data:

1) Complete any transformations on your data in Power Query

2) Sort the data how you would like it to be ordered for calculation

3) As the last step in your query, select the 'Add Column' tab above the ribbon, find th 'Index Column' button and hit it. It doesn't really matter whether your index column starts from 0 or 1, the effect is the same.

Pete

Pete

[Ahmedelshark]

This is great Pete. Thank you so much!