Need help weekly time base comparison power bi

[nardtmo]

Hello All,

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

[Mariusz · Accepted solution]

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.

[nardtmo]

Mariuz,

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

[Amit2019]

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

[nardtmo]

not allowing me to create table