Forum Discussion

qwaiti27231's avatar
qwaiti27231
Icon for Helper III rankHelper III
5 years ago
Solved

Need help urgently match

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

    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's avatar
    qwaiti27231
    Icon for Helper III rankHelper III
    5 years ago

    amitchandak Cant we built as a calculate Column???

     

     

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      5 years ago

      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's avatar
        qwaiti27231
        Icon for Helper III rankHelper III
        5 years ago

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