cancel
Showing results for
Did you mean:
Frequent Visitor

first occurences across months

Hi,

Seeking guidance on how to do this ?

Requirement : if one of the id has repeated in any of the months ,then it should not be considered again for any other month count

Lets say customer visits shop in month of jan ,same customer visits in the feb month also,but when we plot graph for month wise customer count,ideally for feb it should show count which is exclusion of customers apppeared in jan

So graph  needed is - Bar chart(axis-month and bars - count of ids)

For refrence  - please find the attached file,

1 ACCEPTED SOLUTION
Community Support

You need to create a new column to turn the month into a number.

```MonthNum =
SWITCH (
test[Month],
"Jan", 1,
"Feb", 2,
"Mar", 3,
"Apr", 4,
"May", 5,
"Jun", 6,
"Jul", 7,
"Aug", 8,
"Sep", 9,
"Oct", 10,
"Nov", 11,
"Dec", 12
)
```

Then create a new column to display first occurrence.

```Column =
VAR a =
CALCULATE ( COUNTROWS ( test ), ALLEXCEPT ( test, test[ID] ) )
RETURN
IF (
a = 2,
CALCULATE (
FIRSTNONBLANK ( test[ID], 1 ),
FILTER (
test,
test[ID] = EARLIER ( test[ID] )
&& test[MonthNum]
= EARLIER ( test[MonthNum] ) + 1
)
),
test[ID]
)
```

Now you can get the visual.

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

If this post helps, then please consider Accept it as the solution to help the other members find it.
Community Support

You need to create a new column to turn the month into a number.

```MonthNum =
SWITCH (
test[Month],
"Jan", 1,
"Feb", 2,
"Mar", 3,
"Apr", 4,
"May", 5,
"Jun", 6,
"Jul", 7,
"Aug", 8,
"Sep", 9,
"Oct", 10,
"Nov", 11,
"Dec", 12
)
```

Then create a new column to display first occurrence.

```Column =
VAR a =
CALCULATE ( COUNTROWS ( test ), ALLEXCEPT ( test, test[ID] ) )
RETURN
IF (
a = 2,
CALCULATE (
FIRSTNONBLANK ( test[ID], 1 ),
FILTER (
test,
test[ID] = EARLIER ( test[ID] )
&& test[MonthNum]
= EARLIER ( test[MonthNum] ) + 1
)
),
test[ID]
)
```

Now you can get the visual.

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

If this post helps, then please consider Accept it as the solution to help the other members find it.

Announcements