Calculating sums on the go

[Cadenze]

I don't know what to search for, so if my query is old news, please direct me to the answer or give me some search words. I would like to calculate data in a given month that sums up data from the previous month. I will be using it as some kind of projection tool showing planned activities affecting inventory over time. In my table, I have all data forming the basis, and I also have the primo inventory size. Can someone help with a query or method or something to that effect?

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached files.  Please study the measures in these files and adapt them to your data.

[Cadenze]

Thank you so much for offering an alternative solution. I will definitely study your suggestions and learn from them.

[v-huijiey-msft · Accepted solution]

Hi @Cadenze ,

First, create a date table disconnected from the main table.

Date = CALENDAR(MIN('Table'[Reqdate]),MAX('Table'[Reqdate]))

Extract its year and month:

Year = YEAR('Date'[Date])
Month = MONTH('Date'[Date])

Create a calculated column in the main table:

YearMonthSort = YEAR('Table'[Reqdate])*12+MONTH('Table'[Reqdate])

Create a measure:

Measure =
VAR __cur_year =
    SELECTEDVALUE ( 'Date'[Year] )
VAR __cur_month =
    SELECTEDVALUE ( 'Date'[Month] )
VAR __min_date =
    MINX ( ALL ( 'Table'[Reqdate] ), 'Table'[Reqdate] )
VAR __first_value =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        'Table'[Direction] = "Onhand",
        YEAR ( 'Table'[Reqdate] ) = YEAR ( __min_date )
            && MONTH ( 'Table'[Reqdate] ) = MONTH ( __min_date )
    )
VAR __cur_year_month =
    MIN ( 'Date'[YearMonthSort] )
VAR __accumlate_issue =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        'Table'[Direction] = "Issue",
        'Table'[YearMonthSort] < __cur_year_month
    )
VAR __accumlate_receipt =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        'Table'[Direction] = "receipt",
        'Table'[YearMonthSort] < __cur_year_month
    )
VAR __onhand_result = __first_value + __accumlate_issue + __accumlate_receipt
VAR __issue_result =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        'Table'[Direction] = "Issue",
        'Table'[YearMonthSort] = __cur_year_month
    )
VAR __receipt_result =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        'Table'[Direction] = "receipt",
        'Table'[YearMonthSort] = __cur_year_month
    )
VAR __result =
    SWITCH (
        SELECTEDVALUE ( 'Table'[Direction] ),
        "onhand", __onhand_result,
        "issue", __issue_result,
        "receipt", __receipt_result
    )
RETURN
    __result

Drag the year and month of the date table to the Rows of the matrix visual, drag the Direction of the main table to the columns, and drag the created measure to Values.

Now, your needs should be achieved:

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Cadenze]

Wow, I'm impressed. And also acknowledging the fact that I would never arrive at a solution without help. So thank you for your patience and help. 

[Cadenze]

I am sorry, but it seems I don't have that option.

[v-huijiey-msft]

Hi @Cadenze ,

Thank you so much for your prompt reply, it helps us a lot in solving the problem.

Perhaps you could upload your pbix file to a publicly accessible site and include the link in your reply to me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Cadenze]

I don't know where that might be. Publically accessible. I did receive a request to view the pbix-file, but it was from kong fanfey. Was that you? In which case I can grant you access.

[v-huijiey-msft]

Hi @Cadenze ,

Yes, this man is me, then I will ask you to grant permission.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[v-huijiey-msft]

Hi @Cadenze ,

I did not read your requirements, what are you trying to accomplish? Is there an intended visual object?

What does your data mean with and without parentheses?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Cadenze]

Hi Yang, I'm sorry if my question wasn't clear. I have the dataset as shown below and am trying to calculate the values marked with blue text. I have the first OnHand level and then only the issues and receipts going forward. I would like to calculate the resulting OnHand in future. I have the starting value of 200.000.
I have tried with LastNonBlank but am stuck. Ideally it would be something like:
Calculate value as value for demandtype=Issue for last month and value for demandtype=receipt for last month and add it to OnHand for last month. This should give the OnHand for this month.

	Mar	Apr	May	June	July
Issue	(2.400)	(4.000)	(90.000)	(20.000)	(5.000)
Receipt	1.500	4.500	40.000	45.000	3.000
OnHand	200.000	199.100	199.600	149.600	174.600

[v-huijiey-msft]

Hi @Cadenze ,

Please try:

Create a calculated column that calculates the difference between Issue, Receipt:

Monthly Difference = 'Table'[Receipt] - 'Table'[Issue]

Create a calculated column to calculate the money on hand:

Running Total = 
var _min=MINX(ALL('Table'),'Table'[Date])
var _value=MAXX(FILTER(ALL('Table'),'Table'[Date]=_min),[OnHand])
return
IF(MONTH('Table'[Date]) = 3,_value
,
CALCULATE(
    SUM('Table'[Monthly Difference]),
    FILTER(
        ALL('Table'),
        'Table'[Date] < EARLIER('Table'[Date])
    )
) + _value)

The page result is shown below:

The pbix file is attached.

If you have any other queries please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Cadenze]

Thank you - I now understand the importance of sharing the pbix. I have not been explicit enough in my wording and I apologize for the time you have spent on my somewhat inaccurate question. I have made a pbix and uploaded it. I have made the measures you proposed (I think) but the last DAX fails. I would be grateful if you would give it another go - this time with more accurate data and structure.

I can't seem to find a button to upload my pbix. I will find it, but please bear with me.

[Cadenze]

I have sent you a link to my OneDrive. Please let me know if it has reached you.

[v-huijiey-msft]

Hi @Cadenze ,

I am sorry to inform you that I can not access your pbix link.

Perhaps attach the pbix file in the following way.

1. When replying to me, select "browse".

2. Find your pbix file in the path where you stored it and select it to upload.

Please do not log in to your account in Power BI Desktop when providing the pbix file, as this may leak privacy.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Cadenze]

I am sorry, but I do not have the option to upload or attach anything. Please advise.

[v-huijiey-msft]

Hi @Cadenze ,

It is just like sharing your OneDrive Link, except that you have to change a publicly accessible website to upload your pbix files.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Cadenze]

Anyone?