Hello everyone, hope you are all healthy and safe.
I have come up with the solution for Candlestick chart. The data which is required by the candlestick chart I have derived with the help of single DAX i.e. SUMMARIZE function.
The Chart need 5 basic values
1) Date
2) Open value
3) Close value
4) High value
5) Low value
these values I have derived from the large dataset which I have of Ecommerce domain.
Following is the DAX I have used
TableForCandleStick = SUMMARIZECOLUMNS(Orders[OrderDate], FILTER(Orders, Orders[order_status] = "delivered"), "LowValue", CALCULATE(MIN(Order_Payments[payment_value]), Orders[order_purchase_timestamp]), "HighValue", CALCULATE(MAX(Order_Payments[payment_value]), Orders[order_purchase_timestamp]), "OpenValue", CALCULATE(FIRSTNONBLANK(Order_Payments[payment_value], 1), FILTER(Orders, Orders[order_purchase_timestamp] = MIN(Orders[order_purchase_timestamp]))), "CloseValue", CALCULATE(FIRSTNONBLANK(Order_Payments[payment_value], 1), FILTER(Orders, Orders[order_purchase_timestamp] = MAX(Orders[order_purchase_timestamp]))))
Please have a look and let me know in case of any queries.
Stay home, stay safe.
Thank you
Pooja Darbhe
eyJrIjoiNTAyYjU5M2UtYmI4Mi00OGU0LWI5YWEtYjlkNjc2NmJiOTljIiwidCI6Ijg0MmU0ZTBlLTFjNjMtNDE0Ny1iNWJjLTJiOGJhZWQyZjk5OCJ9
