Forum Discussion
Can I do this with a Measure?
I have a table with a vendor name, Vendor attribute, a value, and a date of change.
I would like to find all instances where a vendor attribute was changed twice within 1 week.
I know can do this programmatically and I think I have an idea of how to do this in a measure.
InWeekCount = CountIF(Vendor, Atribute, date>(CurrentRowDate-7)And date<(CurrentRowDate+7))
I just dont know how to write that in DAX.
I think this measure will help me go a long way in understanding how the different contexts apply.
Anyone, please and thank you.
EDIT - Or should I be using a calculated column?
this is air code for a calculated column
InWeekCount = COUNTROWS(
FILTER (Table,
EARLIER ( Table[vName] ) = Table[vName]
&& EARLIER ( Table[vAtribute] ) <> Table[vAtribute]
&& EARLIER ( Table[vDate] ) >= Table[vDate] - 7
&& EARLIER ( Table[vDate] ) <= Table[vDate]
))rarely does my air code work without some tweaks but if you build it step by step it may get you to where you want....
I would think a measure could be done also but this column code was at hand.....
5 Replies
this is air code for a calculated column
InWeekCount = COUNTROWS(
FILTER (Table,
EARLIER ( Table[vName] ) = Table[vName]
&& EARLIER ( Table[vAtribute] ) <> Table[vAtribute]
&& EARLIER ( Table[vDate] ) >= Table[vDate] - 7
&& EARLIER ( Table[vDate] ) <= Table[vDate]
))rarely does my air code work without some tweaks but if you build it step by step it may get you to where you want....
I would think a measure could be done also but this column code was at hand.....
Thanks Cahaba,
I was working toward this but didn't know that I could add the ampersands to nest the filter criteria. I had gotten the Filter only take 2 arguments and started building nested Filter statements.
Once I have My column sorted I will flag your resolution.
Thanks
I did have to tweak it and the below solution is what I did for my actual problem not the simplified version I described.
So I did the following and was getting inconsistent results.
InWkCount = CALCULATE(
COUNTROWS('Raw'),
FILTER(ALL(Raw),
Raw[ENV] = EARLIER(Raw[ENV])
&& Raw[Address Number] = EARLIER(Raw[Address Number])
&& Raw[Data Item] = EARLIER(Raw[Data Item])
&& ABS(Raw[Date Approved]-EARLIER(Raw[Date Approved])<8)))I discovered that there was an extra closing parenthesis at the end.
It closed the ABS function of the last line so the count was of all transactions prior to a date 8 days in the future of the current date. "Cause yeah, thats totally what I was going for."
Once I moved it to where it should be I got what I expected.
InWkCount = CALCULATE(
COUNTROWS('Raw'),
FILTER(ALL(Raw),
Raw[ENV] = EARLIER(Raw[ENV])
&& Raw[Address Number] = EARLIER(Raw[Address Number])
&& Raw[Data Item] = EARLIER(Raw[Data Item])
&& ABS(Raw[Date Approved]-EARLIER(Raw[Date Approved]))<8))
Hi, I_Like_Pi
In your scenario, you can first new a calculated colume(in my test, it is named PreviousDate).
PreviousDate =
MAXX(FILTER(Vendor,EARLIER(Vendor[VendorName])=Vendor[VendorName]&&EARLIER(Vendor[date])>Vendor[date]),Vendor[date])Then, to get the count value, use the below formula.
Count = COUNTROWS(FILTER(ALLEXCEPT(Vendor,Vendor[VendorName]),DATEDIFF(Vendor[PreviousDate],Vendor[date],DAY)<7))
Best regards,
Yuliana GuYuliana, thanks for your reply. I got the result I was looking for so I wont be confirming yours, but you reminded me that I hadn't gone back to flag my item as resolved.
