Split stacked column based on true/false indicator, counting only distinct, latest values

[Mörkö]

Hi.

I’m new to Power BI and I’ve been given some tasks which I can’t get my head around. I’m reporting trades to a third party and single trade can be sent several times on different dates. If it is sent on the same or next day as the trade date, it’s considered to be in time, else it’s sent too late.

I’m trying to setup a stacked column chart which would show number of trades per trading day, split to if it was sent late or in time. Also, the bar chart should have a slicer so we can filter based on when the trades were sent.

My Data_table looks like this. I have merged this in SQL before importing, so the trade_date and trade_id (non-numeric) come from trade fact table, and sent_date is based on another table which has all sent trades and when they were sent. Last column, Late, I can calculate in Power BI based on the difference between sent_date and trade_date.

Sent_date	Trade_date	Trade_id	Late
3.1.2018	3.1.2018	A	FALSE
4.1.2018	3.1.2018	A	FALSE
6.1.2018	3.1.2018	A	TRUE
4.1.2018	3.1.2018	B	FALSE
12.2.2018	3.1.2018	B	TRUE
4.1.2018	4.1.2018	C	FALSE
6.1.2018	4.1.2018	C	TRUE
4.1.2018	4.1.2018	D	FALSE
12.2.2018	4.1.2018	D	TRUE
6.1.2018	5.1.2018	E	FALSE

I’ve setup a Date table which has a range of dates for trade_dates and another Date table for a range of dates for Sent_date. Not sure though if I need a separate Date table for both, or if I could do with just one Date table from 3/Jan to 12/Feb.

• DimTradeDate = CALENDAR(DATE(2018;1;3);DATE(2018;2;12))
• DimSentDate = CALENDAR(DATE(2018;1;3);DATE(2018;2;12))

And I’ve setup an active relationship from those Date tables to trade_date and sent_date separately, with single cross filter direction – if it makes any difference here.

The bar chart works nice for total numbers with just having a measure called Distinct trades and Date from DimTradeDate.

• Distinct trades = DISTINCTCOUNT(Data_table[Trade_id])

Adding the Late column to the chart destroys it, giving too high values instead counting only distinct trades. This should only evaluate the latest sent version of the same trade.

So in the end, if the user was looking at this on 6/Jan (or there’s slicer on Sent_date), all 5 trades should be on the chart – A, B, C, D and E – 2 on 3/Jan (one late, other not), 2 on 4/Jan (one late, other not) and 1 on 5/Jan (not late).

Any help would be appreciated!

[Mörkö]

I guess I need to first filter the data based on sent_date, then find the max sent_date for every trade_id.

Once I have those, join the two values (max sent date and trade id) back to data table and find the trade_date and Late columns.

Or am I on wrong track here?

[v-chuncz-msft]

@Mörkö,

You may try to use RANKX Function. The post below is for your reference.

http://community.powerbi.com/t5/Desktop/How-to-get-the-latest-status-by-id/m-p/260538#M116405