cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Day to Day difference with 2 variables

Hello,

 

I made a db where our printerdata is sent to on a daily basis. 6 properties/OID's are scanned:

  • copy count black
  • copy count color
  • total copy
  • print count black
  • print count color
  • total print

I want to add an extra column wich calculates the difference from the last day but for the same printer and OID.

So far i got something like this:

 

Count(Daily) =
VAR PrintPreviousDay =
CALCULATE (
SUM ( COUNTER[Count] );
FILTER (
ALL ( COUNTER );
COUNTER[Date]
= EARLIER ( COUNTER[Date] )-1
&& COUNTER[PrinterID] = EARLIER ( COUNTER[PrinterID] )
&& COUNTER[TypecounterID] = EARLIER( COUNTER[TypecounterID] )
)
)
RETURN
COUNTER[Count] - PrintPreviousDay
 
But the new column only copies the count value instead of substracting it from the previous day.
PrinterID   TypecounterID    Date         Count      Count(Daily)
2019-10-08_14-23-57.png
 
Any help is appreciated to fix my formula.
 
Thanks
Dries
 
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Day to Day difference with 2 variables

Hi @driesvdb ,

 

To create a measure as below.

difference = 
VAR dat = [Date] - 1
VAR pre =
    CALCULATE (
        SUM ( 'Table'[Count] ),
        FILTER (
            'Table',
            'Table'[PrinterID] = EARLIER ( 'Table'[PrinterID] )
                && 'Table'[TypecounterID] = EARLIER ( 'Table'[TypecounterID] )
                && [Date] = dat
        )
    )
RETURN
    IF ( pre = BLANK (), BLANK (), 'Table'[Count] - pre )

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Day to Day difference with 2 variables

Hi @driesvdb ,

 

To create a measure as below.

difference = 
VAR dat = [Date] - 1
VAR pre =
    CALCULATE (
        SUM ( 'Table'[Count] ),
        FILTER (
            'Table',
            'Table'[PrinterID] = EARLIER ( 'Table'[PrinterID] )
                && 'Table'[TypecounterID] = EARLIER ( 'Table'[TypecounterID] )
                && [Date] = dat
        )
    )
RETURN
    IF ( pre = BLANK (), BLANK (), 'Table'[Count] - pre )

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Highlighted
Regular Visitor

Re: Day to Day difference with 2 variables

Hi @v-frfei-msft 

 

When I enter the lines the collumn is still empty, no calculation seems te be done.

2019-10-09_14-03-53.png

In the link below is the .pbix file ( I don't have enough rights to upload it directly )

https://vgdeu-my.sharepoint.com/:u:/g/personal/dries_van_den_bossche_vgd_eu/ETYtyNXf-yJNrMl001ASATYB...

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors