Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago

Accumulated by dates

Hello community, I hope you can help me with this question

I am trying to look for the accumulated of our stock, without emabargo to program in DAX I am not the best, I could only do this Measure that takes the difference between the inputs and the processed:

PROD TPDM= Inputs

LAFI INPUT = Outputs

Differences = CALCULATE(COUNT('PROD TPDM'[DATE])-COUNT('LAFI ENTRY' [DATE]))
Here's what it does

However I want you to show the accumulated, that is:

MonthinoutDifference*stock
January27,15225,1462,0062,006
February26,04322,4223,6215,627
March29,27033,725-4,4551,172
April31,95829,1812,7773,949
May28,82029,555-7353,214
June409354553,269
…………

*The Difference column I don't want to appear

I hope you can help me

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Syndicate_Admin , I think you need something like this

     

    Inventory / OnHand
    [Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

    Inventory / OnHand =
    CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

     

     

    How to work with more than one date

    https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

  • JChai's avatar
    JChai
    Icon for Advocate I rankAdvocate I
    4 years ago

    Hello Syndicate_Admin,

     

    I believe that you are talking about cumulative sum. Here's how you can do it with DAX:

     

    CUM_DIFF = 
CALCULATE (
SUM('TABLE'[Diferencias]),
FILTER(ALL('TABLE'),
'TABLE'[MONTH NUM] <= EARLIER ( 'TABLE'[MONTH NUM] )
)
)

     

    Notice that I have generated an additional column `MONTH NUM` which help in sorting the `MONTH` (Text) column. Below is how my final table looks like.

    Here are the link that you might find helpful:  Solved: Cumulative Sum, DAX - Microsoft Power BI Community

     

    Hope this help!

     

    Best,

    JKC

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      4 years ago

      It is just what I want to do, without emabargo, my data is not separated by months, the data is individual data and is grouped depending on the month, as seen in the photo, try to make a column with the number of the month, but I do not know if it works that way

      And this columa I made with a Month

      It is also worth mentioning that the production and the entries are in two separate tables

      • JChai's avatar
        JChai
        Icon for Advocate I rankAdvocate I
        4 years ago

        Syndicate_Admin 

        Yes, it can be done. You just need to make sure that you have define the right schema relationships for working with both LAFI & TPDM tables. A simple illustration of this schema is presented as below.

        The results presented from the simple tables in Power BI is shown as below.

        The DAX formula uses are the same without much complications, thus, you should be able to handle easily. 

        Best,

        JKC

        ---

        FYI: LAFI table has 141 rows & TPDM table has 145 rows.