Forum Discussion

cb2222's avatar
cb2222
Regular Visitor
1 year ago
Solved

Calculating a Historical Sum Table

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 th...
  • SamWiseOwl's avatar
    SamWiseOwl
    1 year ago

    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
SamWiseOwl's avatar
SamWiseOwl
Super User
1 year ago

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