Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi, Anonymous 

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      v-lili6-msft
      Community Support
      7 years ago

      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