Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jwi1
Post Patron
Post Patron

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
AkhilAshok
Solution Sage
Solution Sage

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

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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
Solution Sage
Solution Sage

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

Its working, thanks a lot!

 

John

aravind9652
New Member

@jwi1

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.