Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
GunnerJ
Post Patron
Post Patron

Count of Dates in table 1

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])
       )
    )

 

GunnerJ_0-1663594768388.png

 

Sample of what the end product would look like.

NameTou/EV by Campaign 
Back to school gigabit upgrade 202215
Energy Flex Plan Q1 2022221

 

 

1 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())  

View solution in original post

5 REPLIES 5
alena2k
Resolver IV
Resolver IV

@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])
        )
    )
)

@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. 

@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
Resolver IV
Resolver IV

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())  

@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. 

GunnerJ_0-1663600184079.png

 

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)

GunnerJ_1-1663600459302.png

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.