Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a table that stores transaction data regarding items recieved/sold. Depending on selected date I need to filter that table to only the latest record to know what is Total Quantity I have and then calculate number of days it took to sell an Item.
I'm not sure if it is more appropriate to use a measure or create new table that is filtered? I looked into using TOPN however I'm not getting right data back. I know that I need to calculate MAX(Transaction Date) for each Item and then get record corresponding to that date, however I'm not sure how to proceed forward.
Thanks,
@VladyOselsky , Try measure
LASTNONBLANKvalue(Table[Date],Sum(Total[Qty]))
or
calculate(LASTNONBLANKvalue(Table[Date],Sum(Total[Qty])), filter(allselected(Table), Table[Item] = max(Table[Item])))
@amitchandak That function did not work for me, I ended up creating 3 different measures to produce results that I was looking for but I'm not sure if there a way to optimize them to make it more straight forward.
First Measure just gets me max date from my Calendar table that is used as a slicer
MaxSelectedDate = MAX('Calendar'[Date])
Second Measure gets max date for each item id to be able to get the record I need
MaxItemDate =
VAR _md = [MaxSelectedDate]
VAR _ret =
CALCULATE (
MAX ( TransactionRecords[TransactionDate] ),
ALLEXCEPT ( TransactionRecords, TransactionRecords[ItemID] ),
TransactionRecords[TransactionDate] <= _md
)
RETURN
_ret
Third measure finally return the "Last" value. by filtering the TransactionRecords to specific date
MaxQuantity =
VAR maxD = [MaxItemDate]
VAR _value =
CALCULATE (
MAX ( TransactionRecords[Quantity] ),
ALLEXCEPT ( TransactionRecords, TransactionRecords[ItemID] ),
TransactionRecords[TransactionDate] = maxD
)
RETURN
_value
I was able to spot check and results are correct but it only works if I use ItemID column in a table, If I try to use different column like vendor id to try to summarize by vendor it breaks my logic.
User | Count |
---|---|
107 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |