Detailed summary Table

[sovereignauto]

Good Morning,

So once again after some help! 

I have the attached which is an example as orginal has 2 years of data and many more rows and columns (about 10k rows per year for claims and 150k per year for notes) that i need to try to work out how to do the following.. 

" I need to see for each day of the year how many files each handler had open and the % of these where the last note was more than 3 days ago" 

So i have 1 table which contains all file references with the date opend and date closed with handler name (as part of a bigger table),

I then have a notes table with Date of note and reference (as part of a bigger table) 

So im thinking i need to somehow create a date table which summarises by handler how many open files they had and of them open files how many werent contacted "in the notes table" in the last 3 days and then divide one against the other.

EDIT:  DropBox File 

[MFelix · Accepted solution]

Hi @sovereignauto ,

Create a calendar table then add the following measure to your model:

Number claims open =
COUNTROWS (
    FILTER (
        Claims,
        Claims[Date Opend] <= MAX ( 'Calendar'[Date] )
            && Claims[Date Closed ] >= MAX ( 'Calendar'[Date] )
    )
)

PercentageOfNotes =
VAR tempTable =
    SELECTCOLUMNS (
        FILTER (
            Claims,
            Claims[Date Opend] <= MAX ( 'Calendar'[Date] )
                && Claims[Date Closed ] >= MAX ( 'Calendar'[Date] )
        ),
        "Claim", Claims[Ref]
    )
VAR Notes_Values =
    SUMMARIZE (
        FILTER (
            'Notes',
            Notes[Date] <= MAX ( 'Calendar'[Date] )
                && Notes[Date]
                    >= MAX ( 'Calendar'[Date] ) - 2
                && Notes[Ref] IN tempTable
        ),
        Notes[Ref]
    )
RETURN
    DIVIDE ( COUNTROWS ( Notes_Values ), [Number claims open] )

See result below and in attach PBIX file.

[sovereignauto]

@MFelix thank you so much it is a great start! 

Do you know how i would be able to "group by" Handler as well as date? so for each date how many files each handler had?

[MFelix]

Hi @sovereignauto ,

Not sure if I understand what you want to but in the example I send out you just need to add the Handler to the Column series:

In the way the measures are calculated you can use any column on your tables to give further detail.