Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
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
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
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?
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.
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
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.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
20 | |
19 | |
13 | |
12 |
User | Count |
---|---|
42 | |
27 | |
23 | |
22 | |
22 |