Forum Discussion

cmacdonald16's avatar
cmacdonald16
New Member
3 years ago
Solved

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 ne...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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