none
To perform calculation correctly RRS feed

  • Question

  • Hi,

     

    I have prepared a report.Please find the screenshot below:

    The requirement is to pass the previous week's total value in one of the next week's order type(Inventory).

    i.e for the 3rd part, in week 53 the quantity should have value as -451.11 0.00 2945.44 

    week 01 as -428.00 0.00 2494.33 etc. till the last week.

    So can someone please suggest me a method to achieve it through DAX in Power BI?

    Tuesday, January 2, 2018 1:04 PM

Answers

  • Hi there,

    Based on your description I just provided a sample solution with dummy data set.

    I generated a sample data set as following:

    The left one is the fact table and the other 2 are dimension tables with sorting column on side.

    I load them into data model and it looks like following:

    In order to get the result you look for, you should consider the following cases:

    1. the inventory should have an opening balance in the fact table

    2. For demand and inbound supply, the figure listed in the report should be the sum of fact table figure in that period

    3. For inventory figure in the third line, it is a combination of:

    For the starting period, it also should be the sum of fact table figure

    For any other periods, it should be the sum of period to date fact table figure (opening inventory balance + all inbound supply - all demand)

    The first step is to find the opening period number. It can be done by creating a measure like this:


    The next step is to apply the logic mentioned above, it can done via creating following measure:

    And finally, you can get the result as you looking for:

    The 2017-52 period is the beginning period, so the inventory listed for that period is the beginning balance in the fact table (it is the only line for inventory in the fact table). For other period, the inventory line is the sum of inventory plus all inbound supply and minus demand.

    Kind regards,

    Tom Sun

    Tuesday, January 2, 2018 11:54 PM

All replies

  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    With PQ Transpose() and FirstN()
    http://www.mediafire.com/file/4gp5m546ppzj4fr/01_02_18.xls
    http://www.mediafire.com/file/drhd1efvhihmrjd/01_02_18.pdf

    Also see OP's other posts.

    Tuesday, January 2, 2018 8:26 PM
  • Hi there,

    Based on your description I just provided a sample solution with dummy data set.

    I generated a sample data set as following:

    The left one is the fact table and the other 2 are dimension tables with sorting column on side.

    I load them into data model and it looks like following:

    In order to get the result you look for, you should consider the following cases:

    1. the inventory should have an opening balance in the fact table

    2. For demand and inbound supply, the figure listed in the report should be the sum of fact table figure in that period

    3. For inventory figure in the third line, it is a combination of:

    For the starting period, it also should be the sum of fact table figure

    For any other periods, it should be the sum of period to date fact table figure (opening inventory balance + all inbound supply - all demand)

    The first step is to find the opening period number. It can be done by creating a measure like this:


    The next step is to apply the logic mentioned above, it can done via creating following measure:

    And finally, you can get the result as you looking for:

    The 2017-52 period is the beginning period, so the inventory listed for that period is the beginning balance in the fact table (it is the only line for inventory in the fact table). For other period, the inventory line is the sum of inventory plus all inbound supply and minus demand.

    Kind regards,

    Tom Sun

    Tuesday, January 2, 2018 11:54 PM
  • Hi,

    Thank you for your reply!!

    I am using Power BI desktop for preparing my report.

    So do i need to create the columns like Period,Report Unit etc.

    Also can you tell me the formula that you have used for Report Unit

    Wednesday, January 3, 2018 9:20 AM
  • No you don't have to do exactly the same as the example listed.

    The example is just a guide to tell you how to do it.

    If you want me to tell you how to author the formula in your case, I need to see your data model and then I can tell you the answer.

    Don't forget to mark answer as well :)

    Wednesday, January 3, 2018 9:24 AM
  • Hi,

    Thankyou for your help!!

    Please find the link of my pbi file:

    https://drive.google.com/open?id=1XGplFr7YBiTtsNBNQ0Koq6Ea5sApIyHf

    Wednesday, January 3, 2018 9:56 AM
  • Hi,

    Can someone please suggest me a solution?

    Thursday, January 4, 2018 11:57 AM
  • Hi Harshita,

    is this still an issue for you?

    If yes, please post your question in the Power Pivot forum (this is the Power Query forum)

    Thanks.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Tuesday, March 6, 2018 11:31 PM
    Moderator