cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nardtmo Frequent Visitor
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. 

 

 

 

pbi_issue.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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
Amit2019 Frequent Visitor
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
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.

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

nardtmo Frequent Visitor
Frequent Visitor

Re: Need help weekly time base comparison power bi

not allowing me to create table

 

samplehourlyother.PNG

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 121 members 1,582 guests
Please welcome our newest community members: