Need help with date function

[jwi1]

Hi all

Below is a simple table.

I want to calculate the AMOUNT_LEFT with a formula.

In the (explanation) field I have mentioned how the formula looks like.

The mutation_dates must be ranked (first date on top)

Can somebody help me out, thanks!

John

bottle_nr	capacity_bottle_in_kg	mutation_date	amount_used	amount_left	(explanation)
8448	12	5-2-2018	1	11	12-1=11
8448	12	26-2-2018	4	7	11-4=7

[Ashish_Mathur]

Hi,

I am assuming you want to write a measure so that you obtain a dynamic solution for changes made in slicers/filters.  Share the link from where i can download your PBI file.

[AkhilAshok · Accepted solution]

Create a calculated column like below:

amount_left =
VAR CurrentBottleNumber = 'Table'[bottle_nr]
VAR CurrentMutationDate = 'Table'[mutation_date]
VAR CurrentBottleCapacity = 'Table'[capacity_bottle_in_kg]
VAR UsedTableFilter =
    FILTER (
        'Table',
        'Table'[bottle_nr] = CurrentBottleNumber
            && 'Table'[mutation_date] <= CurrentMutationDate
    )
VAR UsedBottleCapcity =
    CALCULATE ( SUM ( 'Table'[amount_used] ), UsedTableFilter )
RETURN
    CurrentBottleCapacity - UsedBottleCapcity

[jwi1]

Its working, thanks a lot!

John

[aravind9652]

@jwi1

that means.... Do you want display recent transacation... ?