Forum Discussion

nardtmo's avatar
nardtmo
Icon for Helper III rankHelper III
7 years ago
Solved

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. 

 

 

 

Custom Visuals
Dashboards
Reports
  • Mariusz's avatar
    Mariusz
    7 years ago

    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

  • Amit2019's avatar
    Amit2019
    Frequent Visitor
    7 years ago

    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's avatar
      nardtmo
      Icon for Helper III rankHelper III
      7 years ago

      not allowing me to create table

       

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      nardtmo
      Icon for Helper III rankHelper III
      7 years ago

      Mariuz,

       

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