Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello All,
monitoring per hour and need to get the difference between today and last week same hour. see sample data below.
Solved! Go to 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.
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.
Mariuz,
thanks.. apologize for late reply.. I tried and for some reason calculated column resulting to current day values see attachment
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
not allowing me to create table
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |