Solved: Need help with date function

Anonymous · ‎10-20-2018

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

AkhilAshok · ‎10-21-2018

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

View solution in original post

Ashish_Mathur · ‎10-21-2018

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

AkhilAshok · ‎10-21-2018

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