Measure so Actual Hours are only applied for shifts this week

[Justin112]

Hi all,

Hope everyone is well. Any help would be greatly appreciated

I was trying to get this measure so it only sumed  the actual hours for the current week. The actuale end is the date that the appoinment occurred on

Actual Hours for this week =

CALCULATE(

    SUM('appointment'[Actual Hours]),

    'appointment'[Actual End]

        IN { (WEEKNUM(TODAY() }

)

Thanks and Kind Regards

[Justin_112]

Thanks for your help that worked on my end 🙂

[aduguid]

can you please mark my answer as the solution

[Justin_112]

Hey 

Sorry I'm quite new to the forum, could you please advise how to mark it as a solution

[aduguid]

In the top right corner of my post if you click the 3 dots, I think you may see an option.

or it may be in the very top dropdown, either way could you let me know as well

[aduguid · Accepted solution]

If you join your appointment table from [Actual End] to a calender table [Date] column, it's just a matter of selecting the the [Week Ending] date from the calendar in a visual. Then the measure only needs to = SUM([Actual Hours])

Here's an example of a calender table you could use.

Calendar = 
VAR _today_date =        TODAY()  
VAR _fiscal_year =       YEAR(EDATE( _today_date, 6))
VAR _fiscal_year_start = DATE ( _fiscal_year - 1, 07, 01)
VAR _fiscal_year_end =   DATE ( _fiscal_year, 06, 30)

VAR _result = 
    ADDCOLUMNS
    (
        //CALENDARAUTO()   //range of dates is calculated automatically based on data in the model
        CALENDAR(_fiscal_year_start, _fiscal_year_end)
        , "Calendar Year Period End",   FORMAT([Date], "yyyy12")
        , "Calendar Year Period Start", FORMAT([Date], "yyyy01")
        , "Calendar Year Period",       FORMAT([Date], "yyyyMM")
        , "Calendar Year Quarter Nbr",  QUARTER([Date])
        , "Calendar Year Quarter",      FORMAT([Date], "\C\Yyyyy \Qq")
        , "Calendar Year Half",         FORMAT([Date], "\F\Yyyyy \H") & ROUNDUP(MONTH([Date]) / 6, 0)
        , "Calendar Year",              YEAR([Date])
        , "Day Name Short",             FORMAT([Date], "DDD")
        , "Day Name",                   FORMAT([Date], "DDDD")
        , "Day Of Week",                WEEKDAY([Date])  
        , "Day",                        DAY([Date])
        , "Fiscal Year Period End",     FORMAT(EDATE([Date], 6), "yyyy12")
        , "Fiscal Year Period Start",   FORMAT(EDATE([Date], 6), "yyyy01")
        , "Fiscal Year Period",         FORMAT(EDATE([Date], 6), "yyyyMM")
        , "Fiscal Year Quarter Nbr",    FORMAT(EDATE([Date], 6), "q")
        , "Fiscal Year Quarter",        FORMAT(EDATE([Date], 6), "\F\Yyyyy \Qq")
        , "Fiscal Year Half",           FORMAT(EDATE([Date], 6), "\F\Yyyyy \H") & ROUNDUP(MONTH(EDATE([Date], 6)) / 6, 0)
        , "Fiscal Year",                YEAR(EDATE([Date], 6))
        , "Is Current FY",              IF(YEAR(EDATE([Date], 6)) = _fiscal_year, 1, 0)
        , "Is Future",                  IF([Date] > _today_date, 1, 0)
        , "Month End",                  EOMONTH([Date], 0)
        , "Month Name Short",           FORMAT([Date], "MMM")
        , "Month Name",                 FORMAT([Date], "MMMM")
        , "Month Start",                DATE(YEAR([Date]), MONTH([Date]), 1)
        , "Month",                      MONTH([Date])
        , "Week Ending",                [Date] + 7 - WEEKDAY([Date], 1) // Saturday
        , "Week Starting",              [Date] - WEEKDAY([Date], 1) + 1 // Sunday
        , "Week of Month",              1 + WEEKNUM([Date]) - WEEKNUM( EOMONTH([Date], -1 ) + 1 )
        , "Week of Year",               WEEKNUM([Date])
        , "Week of Fiscal Year",        IF(MONTH([Date]) < 7
                                        , WEEKNUM([Date], 1) + (WEEKNUM(DATE(YEAR([Date]), 7, 1), 1) - 1)
                                        , WEEKNUM([Date], 1) - WEEKNUM(DATE(YEAR([Date]), 7, 1), 1) + 1)
    )

RETURN
_result