Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Identify 2 different rows under the same table

Hi All,   I have the below table and use the slicer select report date. When the user selected 14-Feb to 28-Feb in the slicer. I want to compare all records from 14-Feb and 28-Feb. Identify which o...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Thanks all,

     

    Let me take some time to apply the above solution and let you know the outcome. 


    Many thanks

    Clement

     

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi Anonymous 
Apologies for the late reply as I was not available on my computer most of the day. However I had the chance to read your reply on my phone and thought about the solution for a while.
I started with creating a disconnected filter table in order to achieve the shape of the report you're looking for.

 

 

Filter Table = { "Only Available in First Date", "Only Available in Last Date", "Available in Both Dates" }

 

 

The measures are as follows:

  • For the first report:

 

 

Qty First Date = 
VAR FirstDateInSlicer =
    MIN ( 'Table'[Report date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Qty] ),
        'Table'[Report date] = FirstDateInSlicer
    )
Qty Last Date = 
VAR LastDateInSlicer =
    MAX ( 'Table'[Report date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Qty] ),
        'Table'[Report date] = LastDateInSlicer
    )

 

 

  • For the 2nd report

 

 

# PO's = 
VAR CurrentFilter =
    SELECTEDVALUE ( 'Filter Table'[Value] )
VAR FirstDateInSlicer =
    MIN ( 'Table'[Report date] )
VAR LastDateInSlicer =
    MAX ( 'Table'[Report date] )
VAR FirstDateTable = 
    FILTER ( 'Table', 'Table'[Report date] = FirstDateInSlicer )
VAR LastDateTable =
    FILTER ( 'Table', 'Table'[Report date] = LastDateInSlicer )
VAR FistDatePOs = 
    SELECTCOLUMNS ( FirstDateTable, "@POs", [Order] )
VAR LastDatePOs = 
    SELECTCOLUMNS ( LastDateTable, "@POs", [Order] )
VAR POsInBothDates =
    INTERSECT ( FistDatePOs, LastDatePOs )
VAR POsInFirstDate =
    EXCEPT ( FistDatePOs, LastDatePOs )
VAR POsInLastDate =
    EXCEPT ( LastDatePOs, FistDatePOs )
VAR POsBoth =
    COUNTROWS ( POsInBothDates )
VAR POsFirstOnly =
    COUNTROWS ( POsInFirstDate )
VAR POsLastOnly =
    COUNTROWS ( POsInLastDate )
RETURN
    SWITCH (
        TRUE (),
        CurrentFilter = "Only Available in First Date", POsFirstOnly,
        CurrentFilter = "Only Available in Last Date", POsLastOnly,
        CurrentFilter = "Available in Both Dates", POsBoth
    )
Total Qty = 
VAR CurrentFilter =
    SELECTEDVALUE ( 'Filter Table'[Value] )
VAR FirstDateInSlicer =
    MIN ( 'Table'[Report date] )
VAR LastDateInSlicer =
    MAX ( 'Table'[Report date] )
VAR FirstDateTable = 
    FILTER ( 'Table', 'Table'[Report date] = FirstDateInSlicer )
VAR LastDateTable =
    FILTER ( 'Table', 'Table'[Report date] = LastDateInSlicer )
VAR FistDatePOs = 
    SELECTCOLUMNS ( FirstDateTable, "@POs", [Order] )
VAR LastDatePOs = 
    SELECTCOLUMNS ( LastDateTable, "@POs", [Order] )
VAR POsInBothDates =
    INTERSECT ( FistDatePOs, LastDatePOs )
VAR POsInFirstDate =
    EXCEPT ( FistDatePOs, LastDatePOs )
VAR POsInLastDate =
    EXCEPT ( LastDatePOs, FistDatePOs )
VAR QtyBoth =
    CALCULATE ( 
        SUM ( 'Table'[Qty] ),
        FirstDateTable,
        POsInBothDates
    )
VAR QtyFirstOnly =
    CALCULATE ( 
        SUM ( 'Table'[Qty] ),
        FirstDateTable,
        POsInFirstDate
    )
VAR QtyLastOnly =
    CALCULATE ( 
        SUM ( 'Table'[Qty] ),
        LastDateTable,
        POsInLastDate
    )
RETURN
    SWITCH (
        TRUE (),
        CurrentFilter = "Only Available in First Date", QtyFirstOnly,
        CurrentFilter = "Only Available in Last Date", QtyLastOnly,
        CurrentFilter = "Available in Both Dates", QtyBoth
    )

 

 

The report looks like this


Here is a link to download the sample file with the solution https://www.dropbox.com/t/v9NVs1q8VLeP2hIU