Forum Discussion

Thulasiram's avatar
Thulasiram
Helper II
2 years ago

To choose date

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.

Need Help

3 Replies

  • ahadkarimi's avatar
    ahadkarimi
    Solution Specialist
    2 years ago

    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
  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    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]))