Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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].

Eric_Zhang's avatar
Eric_Zhang
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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.