Advertiser Frequency - DAX help

[Anonymous]

Hi,

I am wanting to make a Advertiser Frequency report for those that advertise with us.

I have my calendar table

Date               Month       Year          Week of Year

20/01/2017   Jan             2017          3

I have my transaction table

Date              Company          Title                    Revenue

20/01/2017   Bruce Forsyth      Comedy Gold      £50

I am wanting to have a table/matrix that tells me how many customers spend once a week.  This will need to check the weeks somehow... any ideas?

so it looks like

Is there any way I can get this done via DAX?

[Anonymous · Accepted solution]

Managed to do it by

Table 4 = SUMMARIZE(Query1,Query1[TrueCompany],"Number of Weeks",CALCULATE(DISTINCTCOUNT(Query1[FinWeek of Year]),Query1[FinYear/Month] = 201610 || Query1[FinYear/Month] = 201611 ||Query1[FinYear/Month] = 201612 ||Query1[FinYear/Month] = 201701 ||Query1[FinYear/Month] = 201702 ||Query1[FinYear/Month] = 201703 ||Query1[FinYear/Month] = 201704 ||Query1[FinYear/Month] = 201705 ||Query1[FinYear/Month] = 201706 ||Query1[FinYear/Month] = 201707 ||Query1[FinYear/Month] = 201708 ||Query1[FinYear/Month] = 201709,Query1[ttlname] = "Ht"),"Revenue",CALCULATE(sum(Query1[InsertNetCost]),Query1[FinYear/Month] = 201610 || Query1[FinYear/Month] = 201611 ||Query1[FinYear/Month] = 201612 ||Query1[FinYear/Month] = 201701 ||Query1[FinYear/Month] = 201702 ||Query1[FinYear/Month] = 201703 ||Query1[FinYear/Month] = 201704 ||Query1[FinYear/Month] = 201705 ||Query1[FinYear/Month] = 201706 ||Query1[FinYear/Month] = 201707 ||Query1[FinYear/Month] = 201708 ||Query1[FinYear/Month] = 201709,Query1[ttlname] = "Ht"))

[Anonymous]

Hi @Anonymous,

Please share the pbix file with sample data, I will test if it can be achieved.

Notice: Do mask sensitive data before uploading.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous

Link below...

https://drive.google.com/file/d/0B0ozcJ6ZI3zEM1NZWGhhcUgtbFU/view?usp=sharing

So what I would be looking for is a table with 

Number of Companies  Number of Weeks Advertised

1                                     15

1                                     8

and the hopefuly be able to use code instead of the slicer to fit months....

Thanks

C

[Anonymous · Accepted solution]

Managed to do it by

Table 4 = SUMMARIZE(Query1,Query1[TrueCompany],"Number of Weeks",CALCULATE(DISTINCTCOUNT(Query1[FinWeek of Year]),Query1[FinYear/Month] = 201610 || Query1[FinYear/Month] = 201611 ||Query1[FinYear/Month] = 201612 ||Query1[FinYear/Month] = 201701 ||Query1[FinYear/Month] = 201702 ||Query1[FinYear/Month] = 201703 ||Query1[FinYear/Month] = 201704 ||Query1[FinYear/Month] = 201705 ||Query1[FinYear/Month] = 201706 ||Query1[FinYear/Month] = 201707 ||Query1[FinYear/Month] = 201708 ||Query1[FinYear/Month] = 201709,Query1[ttlname] = "Ht"),"Revenue",CALCULATE(sum(Query1[InsertNetCost]),Query1[FinYear/Month] = 201610 || Query1[FinYear/Month] = 201611 ||Query1[FinYear/Month] = 201612 ||Query1[FinYear/Month] = 201701 ||Query1[FinYear/Month] = 201702 ||Query1[FinYear/Month] = 201703 ||Query1[FinYear/Month] = 201704 ||Query1[FinYear/Month] = 201705 ||Query1[FinYear/Month] = 201706 ||Query1[FinYear/Month] = 201707 ||Query1[FinYear/Month] = 201708 ||Query1[FinYear/Month] = 201709,Query1[ttlname] = "Ht"))

[Omega · Accepted solution]

Instead of putting multiple ORs (||), try 

CALCULATE(DISTINCTCOUNT(Query1[FinWeek of Year]), AND(right(Query1[FinYear/Month],2)< 54, left (Query1[FinYear/Month],4)< 2018)),Query1[ttlname] = "Ht"),"Revenue",

[Anonymous]

Thanks @Omega  will give that a try

[Anonymous]

Any ideas anyone?

How I have done it manually is to create a pivot table with the company name in the rows and weeks in the column .  Then I do a count across the rows.  The a count if in that row total column for 

how many 1's twos etc.

Must be an easier way?

[Omega]

Can you share a screenshot of the pivot and the tables you have created?

[Anonymous]

@Omega

Pivot

 Calendar

 

Sales Table

Ad Table

One of the ways I am thinking is if I put in the fin month/week into the Sales table then do a count on that summarized by company?