cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Calculating sums on the go

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?

1 ACCEPTED SOLUTION
Community Support

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!

17 REPLIES 17
Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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

Community Support

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!

Helper I

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.

Helper I

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

Community Support

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

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!

Helper I

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.

Community Support

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!

Community Support

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!

Helper I

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
Community Support

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!

Helper I

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.

Helper I

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

Community Support

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.

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!

Helper I

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

Community Support

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!

Helper I

Anyone?