Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Start & End Dates

I have a list of Shows with a start date and an end date. Some cross years and I need to show the total hours on a platform each month and year. So basically I need for each month a formula that say 'if this date is after start and before end date add the hours for this show"...

 

I have tried a bunch of different formulas and most recently this which is only counting the hours in the End Year.

 

Hours On-Air =
IF(AND(FIRSTDATE('Calendar'[Date])>=(firstdate('Total Programming'[Start])),(firstdate('Calendar'[Date])<=(FIRSTDATE('Total Programming'[End])),
Sum ( 'Total Programming'[# of Hours] ),0)
 
Any Ideas??
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Anonymous ,

    Based on your description, you can try to create this measure:

    Hours = 
VAR _date =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR _start =
    SELECTEDVALUE ( 'Total Programming'[Start] )
VAR _end =
    SELECTEDVALUE ( 'Total Programming'[End] )
RETURN
    IF (
        ISFILTERED ( 'Calendar'[Date] ),
        IF (
            _date > _start
                && _date < _end,
            DATEDIFF ( _start, _date, HOUR ),
            "not correspond"
        ),
        "not correspond"
    )

    Attached a sample file that hopes to help you: date hours.pbix

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

    Based on your description, you can try to create this measure:

    Hours = 
VAR _date =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR _start =
    SELECTEDVALUE ( 'Total Programming'[Start] )
VAR _end =
    SELECTEDVALUE ( 'Total Programming'[End] )
RETURN
    IF (
        ISFILTERED ( 'Calendar'[Date] ),
        IF (
            _date > _start
                && _date < _end,
            DATEDIFF ( _start, _date, HOUR ),
            "not correspond"
        ),
        "not correspond"
    )

    Attached a sample file that hopes to help you: date hours.pbix

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.