Need help urgently match

[qwaiti27231]

Date(m/d/year)      Order id

1/5/2020                    1

1/6/2020                    2

1/7/2020                     3

2/6/2020                  1              

2/6/2020                   2            

2/6/2020                   4

3/6/2020                  3

3/6/2020                  5             

3/6/2020                   6 

when I select the Feb I should get this output

Date(m/d/year)      Order id   Flag

2/6/2020                  1              1

2/6/2020                   2             1

2/6/2020                   4             0

when I select the March I should get this output

Date(m/d/year)      Order id   Flag

3/6/2020                  3              1

3/6/2020                  5             0

3/6/2020                   6            0

the output will be "1" if the order id is present in the previous month. Thank you 

[AlB · Accepted solution]

@qwaiti27231 

The measure I suggested above should work. If you want a calculated column:

Flag = 
VAR currentOrder_ =  Table1[Order id]
VAR limit_ = EOMONTH (  Table1[Date(m/d/year) ] , -1 )
RETURN
    CALCULATE (
        COUNT ( Table1[Order id] ),
        Table1[Order id] = currentOrder_,
        Table1[Date(m/d/year) ] <= limit_,
        ALL ( Table1 )
    ) + 0

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[qwaiti27231]

i wanted calculated column

[AlB]

Hi @qwaiti27231 

Previous month or previous monthS?? Your talk about the former but your example shows the latter. For the latter option:

Flag =
VAR currentOrder_ =
    SELECTEDVALUE ( Table1[Order id] )
VAR limit_ =
    EOMONTH ( SELECTEDVALUE ( Table1[Date] ), -1 )
RETURN
    CALCULATE (
        COUNT ( Table1[Order id] ),
        Table1[Order id] = currentOder_,
        Table1[Date] <= limit_,
        ALL ( Table1 )
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[qwaiti27231]

@AlB previous months

[amitchandak]

@qwaiti27231 , replies are nested, check my last replay with file. Two measures added into the file

[amitchandak]

@qwaiti27231 , Try a new measure like

if(isblank(countx(filter(all(Table), table[date] > eomonth(max(Table[date]),-2) && table[date] <= eomonth(max(Table[date]),-1) && table[Order id] =max(Table[Order id])),[Order Id])),0,1)

[qwaiti27231]

@amitchandak its not working

[amitchandak]

@qwaiti27231 , Please find the attached file after the signature.

[qwaiti27231]

@amitchandak Cant we built as a calculate Column???

[amitchandak]

@qwaiti27231 , A new column like

Column = var _1 = if(isblank(countx(filter(('Table'),  'Table'[Date]  <= eomonth(EARLIER('Table'[Date] ),-1) && 'Table'[Order Id]=EARLIER('Table'[Order Id])),'Table'[Order Id])),0,1)
return if( not(ISBLANK(COUNT('Table'[Order Id]))),_1,BLANK())

This is another option, but the answer does not match. Use the above one

Column 2 = var _1 = if(isblank(countx(filter(('Table'), 'Table'[Date] > eomonth(EARLIER([date]) ,-2) && 'Table'[Date]  <= eomonth(EARLIER('Table'[Date] ),-1) && 'Table'[Order Id]=EARLIER('Table'[Order Id])),'Table'[Order Id])),0,1)
return if( not(ISBLANK(COUNT('Table'[Order Id]))),_1,BLANK())

[qwaiti27231]

@amitchandak ur logic is absolutely correct but its taking too long to query it