Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 tod...
Date
DAX
matrix
  • Whitewater100's avatar
    Whitewater100
    4 years ago

    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.
  • Whitewater100's avatar
    Whitewater100
    4 years ago

    Sorry then 

    subtract the two.

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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?

 

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    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.