topic Re: Show Total amount from the beginning irrespective of date slicer in DAX Commands and Tips

Show Total amount from the beginning irrespective of date slicer

Anonymous — Tue, 05 Jan 2021 11:33:35 GMT

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

Re: Show Total amount from the beginning irrespective of date slicer

amitchandak — Tue, 05 Jan 2021 12:47:14 GMT

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))

Re: Show Total amount from the beginning irrespective of date slicer

littlemojopuppy — Tue, 05 Jan 2021 15:11:54 GMT

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...

Re: Show Total amount from the beginning irrespective of date slicer

littlemojopuppy — Tue, 05 Jan 2021 15:13:45 GMT

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