Forum Discussion
Diff with previous date
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
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
6 Replies
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.
- 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.
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.
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.
