cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VI
Super User VI

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

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Highlighted
Super User IX
Super User IX

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

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

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

Alex1988_0-1593084343098.jpeg

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.

Highlighted
Super User VI
Super User VI

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

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

Highlighted
Frequent Visitor

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

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 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors