Forum Discussion

ndirishkmk's avatar
ndirishkmk
New Member
6 years ago
Solved

Create Summary Status Table Using Detail Dataset (status = calendar date between two date fields)

Hello,

 

I'm trying to create a summary table showing the number of jobs in a status as of a certain date. This logic is based on evaluating 3 date fields. If the "as of" date is between two dates it should be counted. 

 

Detail Table:

JobReceipt DateProcess DateShip Date
12347/17/57/10
12357/37/67/9
12367/57/8 
12377/10  

 

Calc is easy in Excel with a countif but can't get it to work in PBI. Basically for every calendar date, I want to count the jobs that have a receipt date of less than or equal to that date AND have a process date that is greater than that date (this is Status #1). I also want to count the jobs that have a process date of less than or equal to that date AND have a ship date that is greater than that date (Status #2).

 

Summary Table I'm trying to create based on detail table above:

DateCount of Jobs in Status 1Count of Jobs in Status 2
7/1  
7/2  
7/3  
7/4  
7/5  

 

I thought this would be pretty simple but I'm struggling! Any help would be appreciated! Thanks!

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI ndirishkmk,

    You can use following calculate table formula to create a summary table that used to summary raw table records:

    Summary = 
ADDCOLUMNS (
    CALENDAR (
        MINX (
            UNION (
                ALL ( T2[Process Date] ),
                ALL ( T2[Receipt Date] ),
                ALL ( T2[Ship Date] )
            ),
            [Process Date]
        ),
        TODAY ()
    ),
    "Status1 Count", COUNTROWS (
        FILTER (
            T2,
            [Receipt Date] <= EARLIER ( [Date] )
                && [Process Date] >= EARLIER ( [Date] )
        )
    ) + 0,
    "Status2 Count", COUNTROWS (
        FILTER (
            T2,
            [Process Date] <= EARLIER ( [Date] )
                && [Ship Date] >= EARLIER ( [Date] )
        )
    ) + 0
)

    Regards,
    Xiaoxin Sheng

3 Replies