Forum Discussion
DAX calculation help
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...
hi, Anonymous
If you could try this measure
SpoilerMeasure = var _frodate=CALCULATE(MIN('Date'[Date]))var _todate=CALCULATE(MAX('Date'[Date])) returnIF(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
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
hi, Anonymous
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