topic Re: Calculate the increment of counted rows between dates in DAX Commands and Tips

Calculate the increment of counted rows between dates

Terrassa — Fri, 02 Jun 2023 13:26:44 GMT

Hello,

I'm extracting the list of files in some folders every several days but not on a fix frequency. I need to calculate the increment of number of files between two extraction dates.

This is the look of the extraction table:

I have a table visual with the total count per each folder per date:

But I need to calculate the increment of files per folder between dates:

I would like to use a measure to calculate the increment. Can you please help me?

Thank you very much in advance

Re: Calculate the increment of counted rows between dates

johnt75 — Fri, 02 Jun 2023 14:09:00 GMT

You could create a measure like

Increment = VAR CurrentDate = MAX ( 'Table'[Extract date] ) VAR PrevDate = CALCULATE ( MAX ( 'Table'[Extract date] ), 'Table'[Extract date] < CurrentDate ) VAR CurrentNum = COUNTROWS ( 'Table' ) VAR PrevNum = CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Extract date] = PrevDate ) RETURN CurrentNum - PrevNum

Re: Calculate the increment of counted rows between dates

Terrassa — Fri, 02 Jun 2023 14:39:31 GMT

Hello Johnt75,

It works correctly, thank you very much. It just needs one small adjustment. If one folder that had files in the past has been deleted, the increment doesn't calculate the difference correctly, it has blank value. This is the result when creating a matrix with the increment:

The folder "00-Executive Assistant" was deleted, so the countrows doesn't give any value for this folder and the increment is not calculated, it should indicate -10. Same happens in the first row of the table, one file that was in the root folder has been deleted but this is not indicated in the increment.

How can the formula be modified to consider removed folders?

Re: Calculate the increment of counted rows between dates

johnt75 — Fri, 02 Jun 2023 14:43:11 GMT

You could add "+ 0" to the CurrentRows variable. That would force it to return 0 instead of blank and should work out the difference correctly.

Re: Calculate the increment of counted rows between dates

Terrassa — Fri, 02 Jun 2023 15:03:55 GMT

I've tried to add "+0" in all the countrows and the final substraction but the result is exactly the same, I get blanks instead of the actual decrease of file numbers:

Increment =
VAR CurrentDate =

    MAX ( 'Listado'[Fecha extracto] )
VAR PrevDate =
    CALCULATE ( MAX ( 'Listado'[Fecha extracto] ), 'Listado'[Fecha extracto] < CurrentDate )
VAR CurrentNum =  
    COUNTROWS ( 'Listado' ) + 0
VAR PrevNum =  
    CALCULATE ( COUNTROWS ( 'Listado' ) + 0, 'Listado'[Fecha extracto] = PrevDate )
RETURN
    CurrentNum - PrevNum + 0

Re: Calculate the increment of counted rows between dates

johnt75 — Fri, 02 Jun 2023 15:21:26 GMT

I think this is a problem caused by auto exist because everything is in one table. Try creating a table of all the distinct filenames, e.g.

Filename dimension = DISTINCT( 'Listado'[Filename])

link that to your main table in a one-to-many relationship and then use that new column in your visuals.

Re: Calculate the increment of counted rows between dates

Terrassa — Fri, 02 Jun 2023 17:30:02 GMT

I tried this solution but the visuals don't work, now they always give the total amount of files in the new table not considering the extract date.