DAX calculation help

[sivarammoto]

Hi

Need your help and suggestion on achieveing the below

I have sample data like below.

ID	Transaction Type	Qty	From Date	Todate
1234	Opening Stock	50	3/11/2019	3/12/2019
1234	Closing Stock	60	3/11/2019	3/12/2019
1234	Received	20	3/11/2019	3/12/2019
456	Opening Stock	20	3/12/2019	3/13/2019
456	Closing Stock	20	3/12/2019	3/13/2019
456	Received	10	3/12/2019	3/13/2019
456	Opening Stock	30	3/13/2019	3/14/2019
456	Closing Stock	30	3/13/2019	3/14/2019
456	Received	40	3/13/2019	3/14/2019
1234	Opening Stock	30	3/14/2019	3/15/2019
1234	Closing Stock	20	3/14/2019	3/15/2019
1234	Received	20	3/14/2019	3/15/2019

When I select date range from 3/12/2019 to 3/14/2019, I shouldget the ouput like below

456	Opening Stock	20
456	Closing Stock	30
456	Received	50

Opening Stock = 3/12/2019 opening stock i.e 20

Closing stock  = 3/14/2019 - 1 day  i.e 3/13/2019 of clsoing stock i.e 30

received = sum (received stock) between 3/12/2019 to 3/14/2019 i.e  10+40 =50

I tried with combination of IF and SUM DAX logics but no luck .. Any help here  is much appreciated

[v-lili6-msft]

hi, @sivarammoto 

I'm a little confused about your description.

Opening Stock = 3/12/2019 opening stock i.e 20

Closing stock  = 3/14/2019 - 1 day  i.e 3/13/2019 of clsoing stock i.e 30

received = sum (received stock) between 3/12/2019 to 3/14/2019 i.e  10+40 =50

If the date just based on From Date?

For Closing stock why this row data is filtered?

456

Closing Stock

20

3/12/2019

3/13/2019

Best Regards,

Lin

[sivarammoto]

Hi,

User selects  From and Todate. But to make it simple i mentioned previously From date alone

When the user selects From date as 3/12/2019  then on that day opening stock should be picked which is 20

456

Opening Stock

20

3/12/2019

3/13/2019

when the user selects Todate as  3/14 /2019  then on that day of closing stock should be picked which is 30.

456

Opening Stock

30

3/13/2019

3/14/2019

whenn the user selects  From date as 3/12/2019  Todate as  3/14 /2019  then received should be sum of filtered dates

i.e 10+40 = 50

[v-lili6-msft · Accepted solution]

hi, @sivarammoto 

If you could try this measure

Spoiler

Measure = var _frodate=CALCULATE(MIN('Date'[Date]))

var _todate=CALCULATE(MAX('Date'[Date])) return

IF(SELECTEDVALUE('Table'[Transaction Type])="Opening Stock",CALCULATE(SUM('Table'[Qty]),FILTER('Table','Table'[From Date]=_frodate)),

IF(SELECTEDVALUE('Table'[Transaction Type])="Closing Stock",CALCULATE(SUM('Table'[Qty]),FILTER('Table','Table'[Todate]=_todate)),

IF(SELECTEDVALUE('Table'[Transaction Type])="Received",CALCULATE(SUM('Table'[Qty]),FILTER('Table','Table'[From Date]>=_frodate&&'Table'[Todate]<=_todate)))))

result:

and here is pbix file, please try it.

Best Regards,

Lin

[sivarammoto]

Hello Gurus,

Please can some one reply . Thanks in Advance

Regards

Siva