Diff with previous date

[Anonymous]

Hi guys!

Can't get my new column or measure to calculate the value in column  "PrevRows" in Power Bi.

I want to show the customer how tables grow in the databse between the dates.

Hope you can help me out with solving  that.

ReportDate	Table	Count of rows	What report
2017-03-01	Table1	10	PrevReport
2017-03-01	Table2	210	PrevReport
2017-04-20	Table1	30	ThisReport
2017-04-20	Table2	235	ThisReport

And I need to calculate this 

PrevRows	Diff
10	20
210	25

Regards

Tobias

[Greg_Deckler]

Diff = CALCULATE(SUM([Count of rows]),FILTER(Table,[What report] = "ThisReport")) - CALCULATE(SUM([Count of rows]),FILTER(Table,[What report] = "PrevReport"))

Put Table column and this measure into a table.

[Reid_Havens]

Hi Tobias,

You should be able to accomplish this using a DAX Measure or in Power Query. First question. Are the columns "Table" and "What Report" actual columns in the table output? Second question if yes, are the table names or report names fixed or do they change? If they are columns, and they're fixed names. Then you should be able to use two CALCULATE queries for each, and just find the difference between the values.

[Anonymous]

- Column "table" is. I have added column "What report" when tried to solve this.

- The reason for the column "What report" is to have a fixed value.
  (Column "What report" is defind in a related table "Report_tbl", with column "Date","What report")

- The table names are not fixed, it is the top 10 tables in customer database.

[Reid_Havens]

 Ok so this is helping me consider the right direction. My next question then, so for the "current" or "previous report flags, how many distinct table names would be under a distinct date? If it's always two you could use FIRST/LAST NONBLANK functions to call those rows when calculating current or previous report counts. If it's more than two then we'd need to figure out how to find each distinct table name and compare the count values with the current and previous dates.

[Anonymous · Accepted solution]

Thanks for your help.
I have solved it after reading this (https://community.powerbi.com/t5/Desktop/Find-value-for-first-and-last-date-by-person/m-p/62016).

Regards
Tobias

[Anonymous]

The table can only exist one time for "Current" and one time for "Previous".