cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jwi1 Regular Visitor
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_nrcapacity_bottle_in_kgmutation_dateamount_usedamount_left(explanation)
8448125-2-201811112-1=11
84481226-2-20184711-4=7
1 ACCEPTED SOLUTION

Accepted Solutions
AkhilAshok Established Member
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
aravind9652 New Member
New Member

Re: Need help with date function

@jwi1

 

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

AkhilAshok Established Member
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
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.

jwi1 Regular Visitor
Regular Visitor

Re: Need help with date function

Its working, thanks a lot!

 

John