Ho to make a report which give difference between rows in a table which is entered on different date

[Anonymous]

Hi All,

I have an issue while making a report which takes two date as input and gives difference/changes of records which is entered in a table on different batch( one batch per day ).

Below is complete scenario.

I have two tables in below format

Table1

Id	A	B	C	BatchId
1	TRUE	FALSE	FALSE	1
2	FALSE	FALSE	TRUE	1
3	FALSE	TRUE	TRUE	1
1	TRUE	FALSE	FALSE	2
2	TRUE	TRUE	FALSE	2
2	FALSE	FALSE	TRUE	3
3	FALSE	TRUE	TRUE	3

Table 2

BatchId	Date
1	3/3/2017
2	3/4/2017
3	3/5/2017

 Now i want to build a report in which user select "Start Date" and "End Date"

Then the output will show him the changed/added records between two dates only.

Thanks for your help in advance.

[Eric_Zhang]

@Anonymous

If the Id in table1 indicates the batch sequece and may not be continuous. Try to add a calculated column in  table1

RANK =
RANKX (
    FILTER ( Table1, Table1[BatchId] = EARLIER ( Table1[BatchId] ) ),
    Table1[Id],
    ,
    ASC
)

And then create a new calculated table

Table 3 = 
ADDCOLUMNS (
    FILTER (
        CROSSJOIN (
            Table1,
            SELECTCOLUMNS ( Table2, "BatchID_", Table2[BatchId], "Date", Table2[Date] )
        ),
        [BatchID_] = Table1[BatchId]
    ),
    "filterDate", [Date] + Table1[RANK]
)

Then I think you can create a calendar table and link the date to table3. Then filter by filterDate column.

[Anonymous]

@Eric_Zhang 

Thanks, for the suggestion. But I guess i haven't explained my question properly.

Actually, ID in Table1 repesents some other object which is being processed in batches to decide whether it contain A/B/C or not. If it contains any of them then it will be updated as true or false in DB.

So my task is to provide user to choose two dates, and based on that selection the power bi report give him data that which object's content is changed between two dates.

For example: I select two dates as  3/3/17 and 3/4/17.

Then as per data between two dates selected Object2 and Object3 contents have been changed.

I'll get a table or list any thing, which shows me anything which can give information that between two dates  contents for below ids are changed.

Id

A

B

C

2	Added	Added	Deleted
3			Deleted

[Anonymous · Accepted solution]

Here is the solution which i found on Yammer,

Here are the steps:

1. Create a calculated table 'Start Date' as ALL(Table1[Date]). Rename the only column to [Start Date].
2. Create a calculated table 'End Date' as ALL(Table1[Date]). Rename the only column to [End Date].
3. Create a measure [A Status] as
A Status =
VAR vStartA = CALCULATE(MAXA(Table1[A]), TREATAS(VALUES('Start Date'[Start Date]), Table1[Date]))
VAR vEndA = CALCULATE(MAXA(Table1[A]), TREATAS(VALUES('End Date'[End Date]), Table1[Date]))
RETURN IF(vStartA, IF(vEndA, "Blank()", "Deleted"), IF(vEndA, "Added", "Blank()"))
4. Create two more measures [B Status] and [C Status] in a similar fashion.
5. Create a slicer using 'Start Date'[Start Date].
6. Create a slicer using 'End Date'[End Date].
7. Create a table adding [ID], [A Status], [B Status], [C Status].