Calculating a Historical Sum Table

[cb2222]

Hi,

I have a table that contains a column for date created and date completed of each record. I am looking to create a new table to track the number of open records for each day of the year, so that I can display the historical trends of open records.

I've created a new table using the Calendar function, but I am having trouble creating a second column to sum the number of open records for each day. When I try to use the calculate function, I get an error anytime I try to use the date field created with the calendar function.

The two criteria that I am trying to use is the date created must be after the date row of the new table, and the date row needs to be before the date completed.

[SamWiseOwl · Accepted solution]

Hi @cb2222 

Have you joined the tables together or are the seperate?
I've encountered the error before when you are using the built in Calendar table vs the CalendarAuto function.

Sales open on day =

var currDate = [Date] --Current row in this table

RETURN

CALCULATE(

    sum(Sales[Quantity])--Calculation you want to perform

    ,Sales[SaleDate] <= currDate --Sales open before this date

    && Sales[PaymentDate] >= currDate --sales closed after this date

) --use && to do AND tests and || for OR tests