Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Cadenze
Helper I
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?

 

Cadenze_4-1711464280654.png

 

 

1 ACCEPTED SOLUTION
v-huijiey-msft
Community Support
Community Support

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:

vhuijieymsft_0-1711679385106.png

 

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!

View solution in original post

17 REPLIES 17
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

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

v-huijiey-msft
Community Support
Community Support

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:

vhuijieymsft_0-1711679385106.png

 

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!

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
Helper I
Helper I

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

Cadenze_0-1711612615995.png

 

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!

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.

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

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!

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.

 MarAprMayJuneJuly
Issue(2.400)(4.000)(90.000)(20.000)(5.000)
Receipt1.5004.50040.00045.0003.000
OnHand200.000199.100199.600149.600174.600

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:

vhuijieymsft_0-1711535002434.png

 

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!

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.

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

Hi @Cadenze ,

 

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

vhuijieymsft_0-1711590963352.png

 

Perhaps attach the pbix file in the following way.

 

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

vhuijieymsft_1-1711590992533.png

 

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

vhuijieymsft_2-1711591005562.png

 

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!

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

Cadenze_0-1711613935158.png

 

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
Helper I
Helper I

Anyone?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.