Aggr() function with month for distinct count over the months

[Stanislav1]

Hello there!

I am struggling for a while with this one and I need your help. I want to see the distinct count per month for Campaign ID in a line chart using aggr function.

I have this so far: Max(Aggr(Count( {<$(v_SA_Calendar_Dynamic)>} distinct[Campaign Id]),[Date Start]))

The above number (31) is the total campaigns for 2021 and on the bottom you can see the line chart where for some reason the total count is 36 not 31. 

[rubenmarin]

Hi, that could be because some of the campaigns will count for more than one month, this could happen if the month field used in chart is not directly associated with the Date start, maybe it comes from the sales month or other field where one campaign could be in more than one month.

In this case, maybe: 

Count({<$(v_SA_Calendar_Dynamic)>} distinct Aggr(If(Month=Month([Date Start]), [Campaign Id]),Month,[Campaign Id]))

[Stanislav1]

Hi, you are completely correct. Some of the campaigns are in more than one month. I tried your solution: 

Count({<$(v_SA_Calendar_Dynamic)>} distinct Aggr(If(Month=Month([Date Start]), [Campaign Id]),Month,[Campaign Id]))

Still no success here. 

[rubenmarin · Accepted solution]

You can add a table with Month, campaign id and start date and see the relation between these fields, I suppose each campaign id would have only one start date.

Maybe is just: Count(Distinct If(Month=Month([Date Start]), [Campaign Id])

[Stanislav1]

The issue is resolved thank you