Forum Discussion
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 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:
*The RUN_DATE column is the two dates that I'm comparing (today and yesterday).
Same = COUNTROWS(INTERSECT('TODAY',YESTERDAY)) Removed = countrows(except(YESTERDAY,'TODAY')) Added = COUNTROWS(EXCEPT('TODAY',YESTERDAY))
7 Replies
- lbendlin
Super User
Any particular reason for not using EXCEPT and INTERSECT, the functions designed for this?
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-Forum/ba-p/963216
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/1447523
SaiTejaTalasilaHi 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
)
Thank you, Sai Teja. I tried your code but it did not produce the expected results.
Here's a link to sample data.
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)
