cancel
Showing results for
Did you mean:
Frequent Visitor

Need help weekly time base comparison power bi

Hello All,

monitoring per hour and need to get the difference between today and last week same hour. see sample data below.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Re: Need help weekly time base comparison power bi

Hi @nardtmo

You can add a column with below DAX Expression.

```Column =
VAR pwd = DATEADD( YourTable[Date], -7, DAY )
RETURN
YourTable[Books] -
CALCULATE(
SUM( YourTable[Books] ),
ALLEXCEPT( YourTable, YourTable[Student ID], YourTable[Hour] ),
YourTable[Date] = pwd
)```

Regards,
Mariusz

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

4 REPLIES 4
Frequent Visitor

Re: Need help weekly time base comparison power bi

Hi, Below is my DAX approach to solve this -

Step 1 - Created table "A" using CalculateTable DAX function

A = VAR t1 = CALCULATETABLE(sheet1, Sheet1[Date]="Today") RETURN t1

Step 2- Created table "B" using CalculateTable DAX function

B = VAR t2 = CALCULATETABLE(sheet1, Sheet1[Date]="LastWeek") RETURN t2

Step 3- Created table "Final" using NATURALINNERJOIN and SELECTCOLUMNS DAX function

Final =

Var t1 =SELECTCOLUMNS(A,"Student",A[Student]&"","Hrs",A[Hours]&"","Today",A[Books])

Var t2 = SELECTCOLUMNS(B,"Student",B[Student]&"","Hrs",B[Hours]&"","LastWeek",B[Books])

Var result = NATURALINNERJOIN(t1,t2) Return result

Step 4- In this Step, i created another calculated column "Difference in Book" in the "Final" Table (Created in step -3) Difference in Book = Final[Today] - Final[LastWeek]

After above steps, you will get your final table with the required columns. Hope this will help.

Thanks,

Amit Dhiman

Super User

Re: Need help weekly time base comparison power bi

Hi @nardtmo

You can add a column with below DAX Expression.

```Column =
VAR pwd = DATEADD( YourTable[Date], -7, DAY )
RETURN
YourTable[Books] -
CALCULATE(
SUM( YourTable[Books] ),
ALLEXCEPT( YourTable, YourTable[Student ID], YourTable[Hour] ),
YourTable[Date] = pwd
)```

Regards,
Mariusz

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

Frequent Visitor

Re: Need help weekly time base comparison power bi

Mariuz,

thanks.. apologize for late reply.. I tried and for some reason calculated column resulting to current day values see attachment

Frequent Visitor

Re: Need help weekly time base comparison power bi

not allowing me to create table

Announcements

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Power Platform Summit North America

Register by September 5 to save \$200

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 121 members 1,582 guests
Recent signins: