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

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

 

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hello Gurus,

     

    Please can some one reply . Thanks in Advance

     

    Regards

    Siva

  • 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