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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

Add a diff column to a matrix

Hi!

 

I am reporting on the number of devices per tenant over time. I want to add a third column to the matrix below, showing the the difference between the number of devices per tenant between today and the same date last week. I download data to the report once a week.

 

I have created a matrix in Power BI with the following data:

Rows: Tenant

Columns: Date
Values: Devices (count)

 

This is how far I have come (filtered on Top N = 2 latests dates). Can anyone help me adding a new column to this matrix with the difference/delta between the columns below?


PowerBI.png

 

2 ACCEPTED SOLUTIONS
Whitewater100
Solution Sage
Solution Sage

Hello:

 

Assuming you have measure to Count the devices like Total Devices = SUM(Table[Devices])

You can use DATEADD to get the figure from 7 days ago when you are looking at a particular date.

 

One Week Ago = CALCULATE( [Total Devices], DATEADD( Dates[Date], -7, Day ) )
 
I hope this helps.

View solution in original post

Sorry then 

subtract the two.

Difference = [Total Devices] - [Devices 1 week ago]  (the DATEADD measure I just mentioned)

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Hi again! 

A follow-up on this one. I used this function to calculate the number of devices a week ago:

One week ago = CALCULATE(COUNT('KPI-rapport data'[Device]), dateadd('KPI-rapport data'[Dato],-7,DAY))
 
However, sometimes there is a mismatch between the some of the numbers (two examples highlighted below). 
On 04.05 the "one week ago" should be the same number as the 27.04 "this week" number = 97. Why is it showing the value 94 instead?

ally_S_2-1652348586969.png

 

Hi Ali-S:

When I look at the 94 as one week ago for 4-05-2021 , the week before looks like 3-30-2021 which is 94?

I think if you sort your weeks it will look better.

Just want to ask if the Tenant column is from your Date Table? The Data table would be marked as such and has a relationship with your fact table. 

If you want to provide example file I'm happy to check it out from that perspective.

Anonymous
Not applicable

Thanks a bunch! It worked 😄

Your welcome. Great!

Whitewater100
Solution Sage
Solution Sage

Hello:

 

Assuming you have measure to Count the devices like Total Devices = SUM(Table[Devices])

You can use DATEADD to get the figure from 7 days ago when you are looking at a particular date.

 

One Week Ago = CALCULATE( [Total Devices], DATEADD( Dates[Date], -7, Day ) )
 
I hope this helps.

Sorry then 

subtract the two.

Difference = [Total Devices] - [Devices 1 week ago]  (the DATEADD measure I just mentioned)

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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