Count of Dates in table 1

[GunnerJ]

I'm trying to see how many records in the "Rate Sched Change" table fall between the dates of campaigns in the campaign table. My issue is that I'm getting an error starting I have duplicate dates. For the campaign table I know there are duplicate dates but there's no way around it. 

I know having "MAX" might be called into question but without it I get a seperate error stating "single value for column Startdate in table campaign cannot be determine..."

TOU/EV BY CAMPAIGN = 
   CALCULATE(
    DISTINCTCOUNT('Rate Sched Change'[BI_ACCT]),
       DATESBETWEEN(
           'Rate Sched Change'[BI_UPD_DT_TM],
           max(Campaign[StartDate]),
           max(Campaign[EndDate])
       )
    )

Sample of what the end product would look like.

Name	Tou/EV by Campaign
Back to school gigabit upgrade 2022	15
Energy Flex Plan Q1 2022	221

[alena2k]

@GunnerJ I think I understand the goal, but I am missing visualization which you are using to get an error. Maybe you can share file with sample data?

Another idea is to use calculated table:

TOU/EV BY CAMPAIGN TABLE = 
SUMMARIZE(Campaign, Campaign[name], "Rate Change Count",
    CALCULATE( DISTINCTCOUNT('Rate Sched Change'[BI_ACCT]),
                 DATESBETWEEN(
                'Rate Sched Change'[BI_UPD_DT_TM],
                SELECTEDVALUE(Campaign[StartDate]),
                SELECTEDVALUE(Campaign[EndDate])
        )
    )
)

[GunnerJ]

@alena2k 

here's the sample file. 

https://www.dropbox.com/s/sg3bbw1tmn28yhp/Marketing%20Campaigns%20Dashboard%20%283%29.pbix?dl=0

The goal at the moment is to have a simple table layout with the campaign name next to the value of how many account were impacted. Please let me know if this helps out. 

[alena2k · Accepted solution]

@GunnerJ thank you for sharing! It looks like DatesBetween requires dates column, not datetime.

Please try this:

TOU/EV BY CAMPAIGN = 
var tmp =  CALCULATE(DISTINCTCOUNT('Rate Sched Change'[BI_ACCT]),
            'Rate Sched Change'[BI_UPD_DT_TM] >= SELECTEDVALUE(Campaign[StartDate]),
            'Rate Sched Change'[BI_UPD_DT_TM] <= SELECTEDVALUE(Campaign[EndDate])
        )
       
RETURN IF(HASONEVALUE(Campaign[Name]), tmp, BLANK())  

or

TOU/EV BY CAMPAIGN = 
var tmp =   CALCULATE( DISTINCTCOUNT('Rate Sched Change'[BI_ACCT]),
               DATESBETWEEN('Rate Sched Change'[BI_UPD_DT_TM].[Date], 
                SELECTEDVALUE(Campaign[StartDate]), 
                SELECTEDVALUE(Campaign[EndDate])))
       
RETURN IF(HASONEVALUE(Campaign[Name]), tmp, BLANK())  

[alena2k]

hi GunnerJ, is there any relation between Rate Sched Change and Campaign tables? 

If there is none, you need to create a context for the dates selection, which you did using MAX. I would use SELECTEDVALUE instead and supress table total since it does not make sense

TOU/EV BY CAMPAIGN = 
var tmp =    CALCULATE(
    DISTINCTCOUNT('Rate Sched Change'[BI_ACCT]),
       DATESBETWEEN(
           'Rate Sched Change'[BI_UPD_DT_TM],
           SELECTEDVALUE(Campaign[StartDate]),
           SELECTEDVALUE(Campaign[EndDate])
       )
    )
RETURN IF(HASONEVALUE(Campaign[name]), tmp, BLANK())  

[GunnerJ]

@alena2k  thank you for the reply. There is no established relationship between the two tables. 

Unfortunantly, when using the measure provided I got the same kind of error. 

Just to give additional visualization I'm hoping to determine how many accounts (2nd table) had a BI_UPD_DT_TM between the StartDate and EndDate of a given campaign name (1st table)