I have monthly data that looks something like this:
IDCUTOFFDATEPAYMENT_DATE1235612/31/20191/1/20201223312/31/201911/1/20191525312/31/201912/1/2019123561/1/20201/1/2020122331/1/20202/1/2020152531/1/20202/1/2020
Now I want to create a dashboard for the most recent cutoffdate "01/01/2020" in which I need to find the difference in Payment date with previous month. Any suggestions on how I can achieve this?
I was thinking if it is possible to create a calculated field like this:
IDCUTOFFDATEPAYMENT_DATEPREVIOUS_PAYMENT_DATE1235612/31/20191/1/2020null1223312/31/201911/1/2019null1525312/31/201912/1/2019null123561/1/20201/1/20201/1/2020122331/1/20202/1/202011/1/2019152531/1/20202/1/202012/1/2019
And then i can just calculate the difference in the two columns?
Hi Asaif,
There is a function LOOKUP() which can pull a previous value, but it's a table calculation and those can be kinda finicky. Because you know that you always want the 2nd most recent month, I recommend using level of detail (LOD) expressions instead.
This is the calculation I used
{ FIXED [ID]: MAX(
IF [Cutoffdate] =
{ FIXED : MAX(IF [Cutoffdate] < { FIXED : MAX([Cutoffdate]) } then [Cutoffdate] END)}
THEN [Payment Date]
END
)}
This article explains some of the logic I'm using Calculating Rank without Table Functions
Hope this answers your question
