Need help to track changes in Future Orders fall down

[Ozzyabusinni]

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!

[ahadkarimi · Accepted solution]

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!