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",
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
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"))
