Forum Discussion

CSSR's avatar
CSSR
Frequent Visitor
2 years ago
Solved

Calculate a difference in amount between current a previous date

Hi folks,
I have a following table in which I need to create a measure which will calculate a delta for a document (basicaly saldo) between current/selected date and date before for a document. 

 

So for example, saldo for document 1b for April is 3 (calc: 15-12 ). You can see expected results in columns that are in yellow , red font. 

How did I go about this problem ? I could not use a function previousmonth because booking does not need to be in previous mont but later... so I ranked bookings for a document based on date and the latest booking is always 1 and the booking that's before is always 2 or basically by 1 more. I thought that with ranking I can get it done but i got stuck. i used following code but it did not work well:
Do you have any idea to to do it? BTW, I also have a virtual table for calendar that is connected to fact table to easily filter thru time.

 

measure = 

var a_= MAX('TABLE'[Z_Rank])
RETURN

var Prior_ =
                    CALCULATE([AMOUNT],
                               FILTER(ALLSELECTED('TABLE'),
                                           'TABLE'[Z_Rank]= a_-1)                                           
                                           )

var Current_ =
                    CALCULATE([AMOUNT],
                               FILTER(ALLSELECTED('TABLE'),'TABLE'[Z_Rank]= a_)
                                           )

RETURN
var Final_ =
(Current_- Prior_)
RETURN
DIVIDE(Final_,Final_)*Final_

 

 

thank you a lot for your suggestons and time!

much appreciated

 

 

2 Replies