topic Re: Measure so Actual Hours are only applied for shifts this week in DAX Commands and Tips

Measure so Actual Hours are only applied for shifts this week

Justin112 — Thu, 23 May 2024 07:14:04 GMT

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

Re: Measure so Actual Hours are only applied for shifts this week

aduguid — Thu, 23 May 2024 09:24:45 GMT

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

Re: Measure so Actual Hours are only applied for shifts this week

Justin_112 — Thu, 23 May 2024 23:51:36 GMT

Thanks for your help that worked on my end 🙂

Re: Measure so Actual Hours are only applied for shifts this week

aduguid — Thu, 23 May 2024 23:54:54 GMT

can you please mark my answer as the solution

Re: Measure so Actual Hours are only applied for shifts this week

Justin_112 — Fri, 24 May 2024 00:07:22 GMT

Hey

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

Re: Measure so Actual Hours are only applied for shifts this week

aduguid — Fri, 24 May 2024 00:27:48 GMT

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