Forum Discussion

leudyston's avatar
leudyston
Helper I
8 years ago
Solved

Create Measure Stock Balance

Hello my friends, I'm breaking my head with this measure, I need to calculate the stock balance(SALDO), grouping by store(idiloja), product(iditem) and date mov(iddata_movimento). In database, I got sucess with this consult. How do I create this measure in DAX?database

In attachment i have make a test pbix file.

File PBIX



Thanks

 

Leudyston

  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi leudyston

     

    I think I know what you are after but not sure.  Is this close as a calculated measure?

     

    Running Balance = CALCULATE(
				SUM('fmovimentos'[mov_estoque])
				,FILTER(
					ALL('fmovimentos'),
					'fmovimentos'[idloja] = MAX('fmovimentos'[idloja])
					 && 'fmovimentos'[iditem] = MAX('fmovimentos'[iditem])
					 && 'fmovimentos'[iddata_movimento] <= MAX('fmovimentos'[iddata_movimento])
					)
				)
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi leudyston,

     

    Have you achieved your desired result with Phil_Seamark's suggestion? If yes, would you please accept his reply as a solution so that others can find the answer more easily? 

     

    "I need to simulate this image report, in a PivotTable in PivotTable. I found that getting to the BALANCE by product would be able to use the same measure for other contexts. Can you help me?" I'm not clear about this requirement. Is it related to the original one? If yes, please provide more description about it so that I can test for you. If it is a new request, please post your question as a new thread so that more community members can view it.

     

    By the way, to help us better understand your table structure and protect your sensitive data, please simplify your sample data posted here.

     

    Regards,

    Yuliana Gu

5 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    Hi leudyston

     

    I think I know what you are after but not sure.  Is this close as a calculated measure?

     

    Running Balance = CALCULATE(
				SUM('fmovimentos'[mov_estoque])
				,FILTER(
					ALL('fmovimentos'),
					'fmovimentos'[idloja] = MAX('fmovimentos'[idloja])
					 && 'fmovimentos'[iditem] = MAX('fmovimentos'[iditem])
					 && 'fmovimentos'[iddata_movimento] <= MAX('fmovimentos'[iddata_movimento])
					)
				)
    • leudyston's avatar
      leudyston
      Helper I
      8 years ago

      Hello Phil, thank you for your attention !!

       

      This workaround works when I do not use filters in context. When I use the filters, the result changes and takes a long time to appear. I need to simulate this image report, in a PivotTable in PivotTable. I found that getting to the BALANCE by product would be able to use the same measure for other contexts. Can you help me?

       

       

       

       

       

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        8 years ago

        Hi leudyston,

         

        Have you achieved your desired result with Phil_Seamark's suggestion? If yes, would you please accept his reply as a solution so that others can find the answer more easily? 

         

        "I need to simulate this image report, in a PivotTable in PivotTable. I found that getting to the BALANCE by product would be able to use the same measure for other contexts. Can you help me?" I'm not clear about this requirement. Is it related to the original one? If yes, please provide more description about it so that I can test for you. If it is a new request, please post your question as a new thread so that more community members can view it.

         

        By the way, to help us better understand your table structure and protect your sensitive data, please simplify your sample data posted here.

         

        Regards,

        Yuliana Gu