Forum Discussion

en_90's avatar
en_90
Frequent Visitor
1 year ago
Solved

Convert Measure to Column

Hello,   I've created a measure for comparing sales order IDs from the latest two dates (today and yesterday) in my data set. I'm comparing the two dates to see which sales orders were new, removed...
Need Help
Show and Tell
  • lbendlin's avatar
    lbendlin
    1 year ago

     

     

    Same = COUNTROWS(INTERSECT('TODAY',YESTERDAY))
Removed = countrows(except(YESTERDAY,'TODAY'))
Added = COUNTROWS(EXCEPT('TODAY',YESTERDAY))

     

     

SaiTejaTalasila's avatar
SaiTejaTalasila
Super User
1 year ago

Hi en_90 ,

 

You can try indexing logic with custom column -

SO_CAT =

  VAR __TodaySO = 

      COUNTROWS(

        FILTER(

          'TableTop2', 

          [RUN_DATE] = TODAY()

        )

      )

  VAR __TotalSO = 

      COUNTROWS(

        SUMMARIZE(

          'TableTop2', 

          'TableTop2'[RUN_DATE]

        )

      )

  VAR __IndexSO = 

      RANKX(

        'TableTop2', 

        'TableTop2'[RUN_DATE], 

        , ASC, 

        DENSE

      ) 

RETURN

  SWITCH(TRUE(),

    __TodaySO >= 1 && __IndexSO = 1, "NEW",

    __TodaySO >= 1 && __IndexSO > 1, "EXI

STING",

    "REMOVED"

 

I hope it will be helpful.

 

Thanks,

Sai Teja 

  )

en_90's avatar
en_90
Frequent Visitor
1 year ago

Thank you, Sai Teja. I tried your code but it did not produce the expected results. 

 

Here's a link to sample data. 

https://docs.google.com/spreadsheets/d/1bjVFUqdjb4a2FG23mPoxHcSBSdHzVmfG/edit?usp=drivesdk&ouid=117380829471025896168&rtpof=true&sd=true

 

Yesterday's data run had 2885 records. Today's run had 2969. The expected results are as follows:

  • 2816 existing records (2816 carried over from yesterday to today)
  • 153 added records (153 records present in today's data but not in yesterday's data)
  • 69 removed records (69 records present in yesterday's data but not in today's data)