Forum Discussion
Aligning Calculated Measure with the Correct Date
Hello Everyone, I have a measure that calculates picked orders for each day. However the picked/completed orders for the day will only be calculated the next day. I managed to calculate the picked orders but struggling to alighn the measure with the correct required date. For example the 93 orders were picked on the 17th and analysed on the 19th.
This is how I am currently calculating the measure where previous date is a calculated coloumn based on Date. i.e the day prior to the day under date :
I want the table to look like this :
| Date | Required Date | Picked_Orders |
| 2025-02-03 00:00:00 | 2025-01-31 00:00:00 | 119 |
| 2025-02-04 00:00:00 | 2025-02-03 00:00:00 | 223 |
| 2025-02-05 00:00:00 | 2025-02-04 00:00:00 | 169 |
| 2025-02-06 00:00:00 | 2025-02-05 00:00:00 | 102 |
| 2025-02-10 00:00:00 | 2025-02-07 00:00:00 | 87 |
| 2025-02-11 00:00:00 | 2025-02-10 00:00:00 | 98 |
| 2025-02-12 00:00:00 | 2025-02-11 00:00:00 | 82 |
| 2025-02-14 00:00:00 | 2025-02-13 00:00:00 | 73 |
| 2025-02-17 00:00:00 | 2025-02-14 00:00:00 | 93 |
| 2025-02-18 00:00:00 | 2025-02-17 00:00:00 | 0 |
4 Replies
Khomotjo , Try like
Measure
Picked_Orders = CALCULATE(
DISTINCTCOUNT(Orders[Reference]),
FILTER(Orders,Orders[Status]=="Departed" && TRUNC(Orders[Required Date])=Today()
))or create slicer on disconnected dates table and use filter like
Measure
Picked_Orders = CALCULATE(
DISTINCTCOUNT(Orders[Reference]),
FILTER(Orders,Orders[Status]=="Departed" && TRUNC(Orders[Required Date])=Selectedvalues(Date[Date])
))or consider TI
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145sHi Khomotjo ,
To align your Picked_Orders measure with the Required Date, you need to shift the calculation so that the picked orders from the previous day are recorded under the correct required date. Instead of referencing Previous_Day directly, you can use a variable to shift the date appropriately. The following DAX formula achieves this by setting PrevDate as the previous day's Date and ensuring the calculation correctly associates the picked orders with the required date.
Picked_Orders = VAR PrevDate = MAX(Orders[Date]) - 1 RETURN CALCULATE( DISTINCTCOUNT(Orders[Reference]), Orders[Status] = "Departed", Orders[Required Date] = PrevDate )This formula first determines the previous day's date using MAX(Orders[Date]) - 1. It then applies a CALCULATE function to count the distinct Reference values but only when the Status is "Departed" and the Required Date matches the determined PrevDate. This ensures that the orders picked on February 17th appear under the required date of February 17th rather than being misaligned with the date they were analyzed. Let me know if the results need further adjustments!
Best regards,
Thanks DataNinja777
I want the picked orders to reflect under the correct date. All my visuals are built using date (analysis date or date we publish the report). The table has to look like this :
Date Picked_Orders 2025-02-03 119 2025-02-04 223 2025-02-05 169 2025-02-06 102 25 02 10 87 2025-02-11 98 2025-02-12 82 2025-02-14 73 2025-02-17 93 2025-02-18 0 I tried to adjust the measure to this:
Picked_Orders_Test =VAR PrevDate = MAX(Orders[Date]) - 1RETURNCALCULATE(DISTINCTCOUNT(Orders[Reference]),Orders[Status] = "Departed",Orders[Finalised Date] = PrevDate)Below is the result::DataNinja777 I just had an idea. I am thinking I should filter all where the date is 1 day up from the current date and then count all orders finalised on day equals date .
