Calculated Column returns Null value or not all values

[daveghowes]

I am stuck trying to get a calculated column working in PBI, if i try to - or + the results it returns some values but if i dont - or + it returns the value on that row. Long and short of it I am trying to put the date from the next row onto the current row, I have pasted an example below of some sample data 

Incident_Number	Created_Date	Index	Rank	What I get	What I want
1	26/05/2022 10:29	11	2
1	26/05/2022 00:54	10	1	26/05/2022 10:29	26/05/2022 10:29
2	24/05/2022 10:50	9	2		26/05/2022 00:54
2	23/05/2022 08:18	8	1		24/05/2022 10:50
3	28/05/2022 15:20	7	3		23/05/2022 08:18
3	28/05/2022 15:18	6	2	28/05/2022 15:20	28/05/2022 15:20
3	28/05/2022 15:17	5	1	28/05/2022 15:18	28/05/2022 15:18
4	30/05/2022 05:41	4	4		28/05/2022 15:17
4	27/05/2022 05:07	3	3		30/05/2022 05:41
4	27/05/2022 02:07	2	2		27/05/2022 05:07
4	27/05/2022 01:07	1	1	27/05/2022 02:07	27/05/2022 02:07

My Sql query is pulling data and adding Index based on the ticket no and Rank based on Ticket no and the created_Date, 

the Calc column i am using is below, when using this i see a small amount of values. 

NextDate =

VAR var1 = 'Case Workgroup Assignement History (USE)'[Rank]

VAR var2 = 'Case Workgroup Assignement History (USE)'[Index]

RETURN

CALCULATE (

SUM ( 'Case Workgroup Assignement History (USE)'[Created_Date] ),

FILTER ( 'Case Workgroup Assignement History (USE)', 'Case Workgroup Assignement History (USE)'[Rank] = var1 + 1 && 'Case Workgroup Assignement History (USE)'[Index] = var2 )

)

The Index could go upto the millions and Rank can go into the 100s, as mentioned if i change the calc column abve to not - or + it returns a value on every row of the corresponding created_Date. I am wondering if the data being date and time is affecting it , but i cant be sure. 

[v-yiruan-msft · Accepted solution]

Hi  ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can update the formula of the calculated column as below:

Column_NextDate = 
CALCULATE (
    MAX ( 'Case Workgroup Assignement History (USE)'[Created_Date] ),
    FILTER (
        'Case Workgroup Assignement History (USE)',
        'Case Workgroup Assignement History (USE)'[Index]
            = EARLIER ( 'Case Workgroup Assignement History (USE)'[Index] ) + 1
    )
)

Or you can create a meausre as below to get it:

NextDate = 
VAR _selindex =
    SELECTEDVALUE ( 'Case Workgroup Assignement History (USE)'[Index] )
VAR _preindex =
    CALCULATE (
        MIN ( 'Case Workgroup Assignement History (USE)'[Index] ),
        FILTER (
            ALLSELECTED ( 'Case Workgroup Assignement History (USE)' ),
            'Case Workgroup Assignement History (USE)'[Index] > _selindex
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Case Workgroup Assignement History (USE)'[Created_Date] ),
        FILTER (
            ALLSELECTED ( 'Case Workgroup Assignement History (USE)' ),
            'Case Workgroup Assignement History (USE)'[Index] = _preindex
        )
    )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[daveghowes]

Hi,

Sorry for not replying sooner, this worked thank you. I actually realised the problem in my source data which was causing the issue also. Basically on my Rank column the numbering was going 1,3,4,6,7,8,9 etc because some columns were being filtered out. i removed the filter and this fixed the measures. i now need to fix my SQL query to only pull the rows i actually need. 

Thank you so much