Identify 2 different rows under the same table

[Anonymous]

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 order is only available in 14-Feb, which order is only available in 28-Feb, and which order is available on both 14-Feb and 28-Feb. In case, the user selected 21-Feb to 28-Feb, the same comparison applied to 21-Feb and 28-Feb orders. May I know if DAX functions can solve it?

Thanks

Clement

[Anonymous]

Hi tamerj1

Thanks for your prompt response. I found 14 to 28 and 21 to 28 are not correct so the result.

Thanks

Clement

[Anonymous]

Hi tamerj1

Thanks for your help and provide feedback even via the phone. Really appreciate!

The outcome looks good. When I select different start and end report date, I found the outcome is not quite correct. The 3rd table cannot identify the correct category.

Thanks

Clement

[tamerj1]

@Anonymous 

Just checked the file and seems working fine with all selections. I have no idea why it is showing wrong results with you. Please try to redownload as per the new download link https://www.dropbox.com/t/YzOxcv7gVGAJILJ3

[tamerj1]

Hi @Anonymous 
I have updated the code in my previous reply. You can download again with the error corrected.
Have a great day!

[tamerj1]

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

[Anonymous]

Hi Tamerj1

My first chart will be list out qty of slicer selected first and last date

Then I want to have a card or table to indicate both report dates how many PO and qty only available at the first report date, how many PO and qty only available at the last report date. How many PO and qty available at both report dates.

Then understand from PO existing in both report dates. How many PO and qty with same "date" and different "date". Then may a bar chart show diff day (-/+) at x-axis and PO/Qty at y-axis.

Thanks

Clement

[Anonymous]

Hi Tamerj1

The same data set as below. Assume, I selected the range from 14th to 28th in the slicer. PO4 to PO9 are existing in both report dates 14th and 28th.

1. I want to base on the qty column to calculate the total qty of both dates POs (PO4 to PO9). The expect result will be 1200. Ideally, better only based on earliest date (14th) to sum the qty. The result will become 600.

2. I want to compare the date column of both dates POs (PO4 to PO9). If both POs with same date, output PO count and total qty of same date POs. If both PO with different date, output the days different (-/+) with PO count and total qty.

Thanks

Clement

[tamerj1]

@Anonymous 

• Point one is clear. My understanding is that once start and end report dates are selected you want to display a table that contains only the PO numbers which are available in both report dates with quantity of only the first date. Therfore, as per the sample data the grand total will show 600. Is that correct?

Still need more clarification on point 2. My understanding is that this is another report/table that will disply the the two selected report dates. This time we need to compare the date column. If the same PO in both earlier report date and later report date has the same date then we count the PO's that have the same date using one measure (count of same date PO's) and sum the quantity of these PO's using another measure (qty of same date PO's)

anoth teo similar measures to count the number of dissimilar date PO's and their quantity. One more measure to disply the diffence between dates in days. Is that correct?

[Anonymous]

Hi Tamerj1

Point 1 your understanding is correct.

Point 2 in fact I only have 1 table that same as sample date. I did not create any other table for report date or other data. Is it still possible to compare same POs under selected report date to determine how many PO and qty with same "date" and different "date". If they are with different dates, also found out PO count and qty per day different. e.g.

Same date PO = 3 POs and 300 qty (PO7-PO9)

Diff date PO = 3 POs and 300 qty (PO4-PO6)

2 days diff = 3 POs and 300 qty (PO4-PO6) (in case we have other days diff like, will also list out each days diff PO and qty).

or just simply list out no of days diff PO and qty, then I can assume day diff is 0 meaning same date.

Thanks

Clement

[tamerj1]

@Anonymous 

Ok I think this is almost the same as my initial understanding. But how are you planning to display the results? In card visuals?

[Anonymous]

Hi All,

If I want to calculate the total qty of each category (first date only/second date only/both dates), it is possible to use calculate sum qty filter each category? I tried but seems syntex error. Also, from both dates category, I also want to identify how many POs are with different "date" column value and how many day different.

Thanks

Clement  

[tamerj1]

Hi @Anonymous 

I don't see category in your sample data. Can you please advise how would be thevexpected results based on the given sample data?

[Anonymous]

Dear both,

I just tested it. It works perfectly. Many thanks for your help. It really helps.

Best Regards

Clement

[johnt75]

If you just need the numbers which are in the 3 categories ( first date only, second date only, both ) then you can create 3 measures like

First date only =
var minDate = MIN('Table'[Reporting Date])
var maxDate = MAX('Table'[Reporting Date])
var ordersFirstDate = CALCULATETABLE( VALUES('Table'[Order]), 'Table'[Reporting Date] = minDate)
var ordersSecondDate = CALCULATETABLE( VALUES('Table'[Order]), 'Table'[Reporting Date] = maxDate)
return COUNTROWS(EXCEPT( ordersFirstDate, ordersSecondDate ) )

Second date only =
var minDate = MIN('Table'[Reporting Date])
var maxDate = MAX('Table'[Reporting Date])
var ordersFirstDate = CALCULATETABLE( VALUES('Table'[Order]), 'Table'[Reporting Date] = minDate)
var ordersSecondDate = CALCULATETABLE( VALUES('Table'[Order]), 'Table'[Reporting Date] = maxDate)
return COUNTROWS(EXCEPT( ordersSecondDate, ordersFirstDate ) )

Both dates =
var minDate = MIN('Table'[Reporting Date])
var maxDate = MAX('Table'[Reporting Date])
var ordersFirstDate = CALCULATETABLE( VALUES('Table'[Order]), 'Table'[Reporting Date] = minDate)
var ordersSecondDate = CALCULATETABLE( VALUES('Table'[Order]), 'Table'[Reporting Date] = maxDate)
return COUNTROWS(INTERSECT( ordersSecondDate, ordersFirstDate ) )

[Anonymous · Accepted solution]

Thanks all,

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

Many thanks

Clement

[tamerj1]

Hi @Anonymous 

the slicer is based on which column? Or do you have a date table? If so to which column it connects?

[Anonymous]

Hi 

The slicer column is based on the report date column. I did not create another date table.

Thanks

Clement 

[tamerj1]

@Anonymous 
You can start with creating a slicer table from the report date column

Date Selection =
VALUES ( Table[Report Date] )

Then create the slicer from the new table.
Now use thrf oloowing code to create a filter measure

Filter Measure =
VAR FirstDateInSlicer =
    MIN ( 'Date Selection'[Report Date] )
VAR LastDateInSlicer =
    MAX ( 'Date Selection'[Report Date] )
VAR CurrentDateInVisual =
    MAX ( Table[Report Date] )
RETURN
    OR (
        CurrentDateInVisual = FirstDateInSlicer,
        CurrentDateInVisual = LastDateInSlicer
    )

Place the filter measure in the filter pane and select "is" and insert value "1" then apply.

[Whitewater100]

Hi Clement:

One way to do this is to have an extra slicer just for report date. You'll see the report date slicer on the left and then an overall date slicer on the right. For the report dates I manually entered the three dates in (Enter Data) but you can also paste  excel data there if you'd like instead.If you only want to group by the 3 report dates you may not need the All Date Slicer. I am attachttps://drive.google.com/file/d/12Epr69lOgr33NTPZDYg4XRubqqeTjFQW/view?usp=sharing hing a link. 

[Anonymous]

Hi

Thanks for your response. However, I expect the selection will be 2 report dates, the measure needs to compare both reports dates data to see how many orders on the first report date are not available on the 2nd report date, how many orders on the 2nd report date but are not available in the first report date, how many orders are both existing in first and 2nd report dates.

Thanks

Clement