Forum Discussion
Advertiser Frequency - DAX help
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 J...
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"))
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",
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?
