cancel
Showing results for
Did you mean:
Regular Visitor

Need help with date function

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
1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

Re: Need help with date function

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```
4 REPLIES 4
New Member

Re: Need help with date function

@jwi1

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

Established Member

Re: Need help with date function

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```
Super User

Re: Need help with date function

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.

Regular Visitor

Re: Need help with date function

Its working, thanks a lot!

John