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 is complete scenario.

 

I have two tables in below format

Table1

IdABCBatchId
1TRUEFALSEFALSE1
2FALSEFALSETRUE1
3FALSETRUETRUE1
1TRUEFALSEFALSE2
2TRUETRUEFALSE2
2FALSEFALSETRUE3
3FALSETRUETRUE3

 

Table 2

BatchIdDate
13/3/2017
23/4/2017
33/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.

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

3 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft 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.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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.

      IdABC
      2AddedAddedDeleted
      3  Deleted
      • Anonymous's avatar
        Anonymous
        Not applicable
        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].