Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Terrassa
Helper I
Helper I

Calculate the increment of counted rows between dates

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:

Terrassa_2-1685712268451.png

 

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

Terrassa_0-1685712365051.png

 

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

Terrassa_1-1685712390822.png

 

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

 

Thank you very much in advance

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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

View solution in original post

6 REPLIES 6
johnt75
Super User
Super User

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:

 

Terrassa_0-1685716122937.png

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors