Forum Discussion
Help with a report
I am building a report
I have a power BI table called Sessions, A calculated measure called hours, and two calculated columns Week start date, and Week end date. How can i write a query to for a new calculated measure that will give me the sum of hours between Week start date and Week end date
Hi cmacdonald16 ,
I created some data:
Here are the steps you can follow：
1. Create calculated column.
Week = WEEKNUM('Table'[Date],1)Week start date = MINX(FILTER(ALL('Table'), YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&& 'Table'[Week]=EARLIER('Table'[Week])),[Date])Week start date = MINX(FILTER(ALL('Table'), YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&& 'Table'[Week]=EARLIER('Table'[Week])),[Date])2. Create measure.
Measure = DATEDIFF( MAX('Table'[Week start date]),MAX('Table'[Week end date]),HOUR)3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
1 Reply
Hi cmacdonald16 ,
I created some data:
Here are the steps you can follow：
1. Create calculated column.
Week = WEEKNUM('Table'[Date],1)Week start date = MINX(FILTER(ALL('Table'), YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&& 'Table'[Week]=EARLIER('Table'[Week])),[Date])Week start date = MINX(FILTER(ALL('Table'), YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&& 'Table'[Week]=EARLIER('Table'[Week])),[Date])2. Create measure.
Measure = DATEDIFF( MAX('Table'[Week start date]),MAX('Table'[Week end date]),HOUR)3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly