Relationship Between date and Hours to Start Date and End Date of Subscription

[Alex1988]

Hi All, 

Brand new to Power BI but used to use Qlik. 

We have a a list of users and their Billing Cycles ( And success Status) in one fact table and a  Master calendar in the next. We are using Direct Query. 

I want to be able to select a date or use any attributes from the Master Calendar and see all the billing cycles that date is within. 

so if i select a day, week, hour month etc i see all the billing cycles where that day, week, hour, month etc is between the cycle startdate and end date. 

I'm not sure if this is DAX or a relationship thing but would really apprecaite some help. 

Thanks people 🙂 

Alex

[mahoneypat · Accepted solution]

Please try this approach to get your desired result.  I wasn't sure if you have a relationship between the tables, but this should work either way.  It should count the cycles that overlap with the Date range in scope in your visual (Day, Month, etc.).

Cycles =
VAR __mindate =
    MIN ( 'Date'[Date] )
VAR __maxdate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Cycles ),
        ALL ( 'Date' ),
        Cycles[StartTime] <= __maxdate,
        Cycles[StopTime] >= __mindate
    )

. 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Alex1988]

Will this still work if say the chart has a heirachy and the uses changes from dates to month and there is no longer any filtration on the Date field? 

Thanks, 

Alex 

[amitchandak]

@Alex1988 , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

in direct query mostly measures will work, so need check some sample data

[Alex1988]

Hello ! Thank you for the response. 

So on the left is the billing cycles fact table and on the right a pretty typical Calendar table. When i use date, week, month, year etc in my visualisation - i want Power BI to filter to all the billing cycles ranges where the date is between the start and end date.