Calculate the Number of Days since the Date in a column

[ozhug]

Hi All,

I have a table with machine and filedate.

Machine	filedate
100	10 feb 2018
100	12 feb 2018
100	15 feb 2018
101	11 feb 2018
101	15 feb 2018

I want to know the number of days since the last file was received.

I also have a date table with all dates.

Date(from date table)	FileDate (machine)	machine	Days Since Last File
10 feb 2018	10 feb 2018	100	0
11 feb 2018			1
12 feb 2018	12 Feb 2018	100	0
13 feb 2018			1
14 feb 2018			2
15 feb 2018	15 Feb 2018	100	0
10 feb 2018
11 feb 2018	11 Feb 2018	101	0
12 feb 2018			1
13 feb 2018			2
14 feb 2018			3
15 feb 2018	15 Feb 2018	101	0

Table Filtered for dates 10 Feb – 15 Feb.

I can get the date of the lastfiledate

 LastSavedDate = MAXX(

    FILTER(Machine,Machine[filedate]<EARLIER(Machine[filedate])

        &&Machine[MACHINE]=EARLIER(Machine[MACHINE])),

   Machine[filedate]  )

But have not been able to get to work for each day in date table.

any help greatly appreciated.

thanks

[Phil_Seamark · Accepted solution]

HI @ozhug

This calculated table is pretty close

Table = 
VAR DateTable = CALENDAR(MIN('Table1'[filedate]),MAX('Table1'[filedate]))
VAR WorkingTable = 
ADDCOLUMNS(
        GENERATE(
                DateTable ,
                VALUES(Table1[Machine])
                ),
                "Machine (FileDate)",MINX(
                                        FILTER(
                                            'Table1',
                                            'Table1'[filedate] = [Date] &&
                                            'Table1'[Machine] = EARLIER('Table1'[Machine])
                                            )
                                    ,'Table1'[filedate])               
                         )
    
RETURN ADDCOLUMNS(
        WorkingTable,
        "Days Since Last File",DATEDIFF(
        MAXX(
            FILTER(WorkingTable, 
                    'Table1'[Machine] = EARLIER('Table1'[Machine]) &&
                    [Date] <= EARLIER([Date])),[Machine (FileDate)]),[Date],DAY))

[ozhug]

hi Phil,

Thanks when i got to this part 

RETURN ADDCOLUMNS(
WorkingTable, "Days Since Last File",
DATEDIFF( MAXX(
FILTER(WorkingTable, 'Table1'[Machine] = EARLIER('Table1'[Machine]) && [Date] <= EARLIER([Date])),[Machine (FileDate)]),[Date],DAY))

the FILTER(WorkingTable,

prevents me from using 'Table1'[Machine] , I used WorkingTable[Machine] and it is working so all good.

There is a wrinkle,

When i put this table into matrix on a report page with a slicer for machine (it still shows all machines) not just the machines selected in the slicer.

Thanks

P.S.  Also need to have a closer look and understand how this works

[Phil_Seamark]

Which field are you using for your slicer?  The [machine] field from the new calculated table?  or the [machine] field from the original table?

[ozhug]

so sorry my bad, i messed up my relationships.
It works great.

thank you very much.