Need help to track changes in Future Orders fall down

Ozzyabusinni — Mon, 26 Aug 2024 15:43:37 GMT

Hi everyone,

I have a Power BI report connected to SQL, and I’m looking to track changes in one of the live tables.

The Order table contains future orders, and I want to monitor the Fall Down percentage.

Is there a way to create a static table with a daily timestamp to count the number of orders per Customer for the current month for each day?

Appreciate the help!

Re: Need help to track changes in Future Orders fall down

ahadkarimi — Mon, 26 Aug 2024 15:52:56 GMT

Hi Ozzyabusinni, give this a try, and if you encounter any issues, let me know.

Create a Date Table: Ensure a Date table exists for the current month.

Capture Daily Snapshot: Use a SQL job or Power Automate to insert daily order counts into a new SQL table with a timestamp.

Create Static Table in SQL:

CREATE TABLE OrderSnapshot ( SnapshotDate DATE, CustomerID INT, OrderCount INT );

Insert daily counts:

INSERT INTO OrderSnapshot (SnapshotDate, CustomerID, OrderCount) SELECT GETDATE(), CustomerID, COUNT(OrderID) FROM Orders WHERE OrderDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) GROUP BY CustomerID;

Calculate Fall Down Percentage in Power BI:

FallDownPercentage = VAR PreviousDayOrderCount = CALCULATE(SUM(OrderSnapshot[OrderCount]), FILTER(OrderSnapshot, OrderSnapshot[SnapshotDate] = TODAY()-1)) VAR CurrentDayOrderCount = SUM(OrderSnapshot[OrderCount]) RETURN IF(PreviousDayOrderCount > 0, DIVIDE(CurrentDayOrderCount - PreviousDayOrderCount, PreviousDayOrderCount, 0), 0 )

Did I answer your question? If so, please mark my post as the solution!✔️
Your Kudos are much appreciated! Proud to be a Responsive Resident!

topic Need help to track changes in Future Orders fall down in DAX Commands and Tips

Need help to track changes in Future Orders fall down

Re: Need help to track changes in Future Orders fall down