Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Community,
I need to set a Column with a 1 or 0 if a value in a column is new in the Current Month (1) or if it is the same in previous month (0). How can I do that? I have tried many ways but I haven't been able to get to it.
Here is a sample
Month | Agreement | Is Agreement New? |
12/1/2020 | 10 | 1 |
12/1/2020 | 11 | 1 |
12/1/2020 | 29 | 1 |
12/1/2020 | 21 | 1 |
12/1/2020 | 20 | 1 |
12/1/2020 | 11 | 1 |
1/1/2021 | 11 | 0 |
1/1/2021 | 40 | 1 |
1/1/2021 | 29 | 0 |
Thanks...
Solved! Go to Solution.
you can create a column
Column = if(ISBLANK(maxx(FILTER('Table','Table'[Agreement]=EARLIER('Table'[Agreement])&&'Table'[Month]<EARLIER('Table'[Month])),'Table'[Month])),1,0)
Proud to be a Super User!
Hi,
This calculated column formula works
=if(ISNUMBER(CALCULATE(COUNTROWS(Data_2),FILTER(Data_2,Data_2[Agreement]=EARLIER(Data_2[Agreement])&&Data_2[Month]<EARLIER(Data_2[Month])))),0,1)
Hope this helps.
Hi @achinchilla
You want a DAX calculated column or M? Here is one DAX column
Is Agreement New? =
VAR PreMonth = DATEADD(AgreementTable[Month],-1,MONTH)
VAR CurAgreement = AgreementTable[Agreement]
VAR T1 = SELECTCOLUMNS(FILTER(ALL(AgreementTable),AgreementTable[Month]=PreMonth),"NEW",[Agreement])
RETURN
IF(CurAgreement IN T1,0,1)
you can create a column
Column = if(ISBLANK(maxx(FILTER('Table','Table'[Agreement]=EARLIER('Table'[Agreement])&&'Table'[Month]<EARLIER('Table'[Month])),'Table'[Month])),1,0)
Proud to be a Super User!
Hi Ryan_mayu
Great solution. It solved what we were looking for.
Regards,
Albin
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |