Show Total amount from the beginning irrespective of date slicer

[Anonymous]

Hi All,

Following is my sample data,

Product     Date                Amount      

A             10-01-2020        1000

A             02-02-2020          500

A             21-03-2020          700

B             19-01-2020          500
B             11-02-2020        1500
B             23-03-2020          400

In my table visual i need these columns as follows and this my expected output,

Product   Net Amount  Balance

A                 500              1500

B                1500             2000

In the report, i have selected range of date in between date slicer as from 01-02-2020 to 29-02-2020

Net Amount is the sum of Amount between the selected date range. But, Balance is the sum of amount from the start date (first non blank value in the date column ie..10-01-2020) to the selected max date (ie...29-02-2020) in the between date slicer.

I nedd a dax formula (measure) to calculate Balance of Amount.

Thanks in advance

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

calculate(sum(Table[Value]), filter(all(Table), Table[Product] = max(Table[Product])))

If date is coming from a date table joined to this

try like

calculate(sum(Table[Value]), all(Date))

[littlemojopuppy · Accepted solution]

Hi @Anonymous 

Try these

Total Amount = 
    CALCULATE(
        SUM(Amounts[Amount]),
        VALUES('Calendar'[Date])
    )

Product Total = 
    CALCULATE(
        SUM(Amounts[Amount]),
        FILTER(
            ALL(Amounts[Product]),
            Amounts[Product] = SELECTEDVALUE(Products[Product])
        ),
        FILTER(
            ALL('Calendar'[Date]),
            'Calendar'[Date] <= MAX('Calendar'[Date])
        )
    )

Net Amount = [Product Total] - [Total Amount]

Results...

[littlemojopuppy]

Assumes you have both a date table and a master products table.  You can download the pbix file here.

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

calculate(sum(Table[Value]), filter(all(Table), Table[Product] = max(Table[Product])))

If date is coming from a date table joined to this

try like

calculate(sum(Table[Value]), all(Date))