To choose date

[Thulasiram]

Dear all,  I have the following table visual.  
the column "Bal minus credit" is created by a measure.

I want the correspending date of the last negative value and would like to minus that date with a fixed date
for example "31-03-2024".  How to achieve that?  Appreciate help.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[amitchandak]

@Thulasiram , Create a date table, join with date of you table

Last Negative Date =
CALCULATE(
MAXX(filter(Values('Date'[Date]),[Bal minus credit] < 0), [Date]), filter( all('Date'), 'Date'[Date] <= Max('Date'[Date])

))

Date Difference =
VAR FixedDate = DATE(2024, 3, 31)
VAR LastNegDate = [Last Negative Date]
RETURN
DATEDIFF(LastNegDate, FixedDate, DAY)

or

Value Difference =
VAR LastNegDate = [Last Negative Date]
RETURN
[Closing Balanace] - calculate ([Closing Balanace] , filter( all('Date'), 'Date'[Date] = [Last Negative Date]))

[ahadkarimi]

hi @Thulasiram create these two Measures and let me know if you encounter into any issues.

Find the last negative value date:

LastNegativeDate = 
CALCULATE(
    MAX('Table'[Date]),
    FILTER(
        'Table',
        'Table'[Bal minus credit] < 0
    )
)

Difference between the last negative date and the fixed date:

DateDifference = 
DATEDIFF(
    [LastNegativeDate],
    DATE(2024, 3, 31),
    DAY
)

Did I answer your question? If so, please mark my post as the solution! Your Kudos are much appreciated! Proud to be a Resolver II