Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nardtmo
Helper II
Helper II

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. 

 

 

 

pbi_issue.PNG

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

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.

View solution in original post

4 REPLIES 4
Mariusz
Community Champion
Community Champion

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 attachmentsamplehourly.PNGsamplehourly1.PNG

Amit2019
Frequent Visitor

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

 

samplehourlyother.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.