Convert Measure to Column

[en_90]

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 or carried over day to day (latest two dates). This works as a measure but I need to convert it into a column but I am unable to.

Any help on convertting this into a column is appreciated!

Measure code:

SO_CAT =

  VAR __TodaySO = COUNTROWS(FILTER('TableTop2',[RUN_DATE]=TODAY()))

  VAR __TotalSO = COUNTROWS(SUMMARIZE('TableTop2',[RUN_DATE]))

RETURN

  SWITCH(TRUE(),

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

    __TodaySO >= 1 && __TotalSO > 1,"EXISTING",

    "REMOVED"

  )

*The RUN_DATE column is the two dates that I'm comparing (today and yesterday).

[SaiTejaTalasila]

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]

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=1173...

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)

[lbendlin]

Any particular reason for  not using EXCEPT and INTERSECT, the functions designed for this?

[en_90]

I am relatively new to DAX so I didn't even know those functions exist until now.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[en_90]

Here's a link to sample data. 

https://docs.google.com/spreadsheets/d/1bjVFUqdjb4a2FG23mPoxHcSBSdHzVmfG/edit?usp=drivesdk&ouid=1173...

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)

[lbendlin · Accepted solution]

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