Split single row of data in Power BI data source into multiple rows












0















I have a table in a Power BI data source with a column for term start and term end date (term length can be longer than a month), along with meta data on the term. I need to report on status of purchased terms as at the end of each month. As far as I can see, the best way of accomplishing this would be to create a calculated table with an entry for each month on which a term is active at its end.



For example, an entry in the original table with the following data:



TermStartDate TermEndDate PurchaseAmount
2018-01-03 2018-04-12 100


Would end up in the calculated table as follows:



MonthPurchased PurchaseAmount
2018-01 100
2018-02 100
2018-03 100


How to accomplish this? Is there a better way than creating a separate calculated table to get this data? Any help or advise is appreciated










share|improve this question





























    0















    I have a table in a Power BI data source with a column for term start and term end date (term length can be longer than a month), along with meta data on the term. I need to report on status of purchased terms as at the end of each month. As far as I can see, the best way of accomplishing this would be to create a calculated table with an entry for each month on which a term is active at its end.



    For example, an entry in the original table with the following data:



    TermStartDate TermEndDate PurchaseAmount
    2018-01-03 2018-04-12 100


    Would end up in the calculated table as follows:



    MonthPurchased PurchaseAmount
    2018-01 100
    2018-02 100
    2018-03 100


    How to accomplish this? Is there a better way than creating a separate calculated table to get this data? Any help or advise is appreciated










    share|improve this question



























      0












      0








      0








      I have a table in a Power BI data source with a column for term start and term end date (term length can be longer than a month), along with meta data on the term. I need to report on status of purchased terms as at the end of each month. As far as I can see, the best way of accomplishing this would be to create a calculated table with an entry for each month on which a term is active at its end.



      For example, an entry in the original table with the following data:



      TermStartDate TermEndDate PurchaseAmount
      2018-01-03 2018-04-12 100


      Would end up in the calculated table as follows:



      MonthPurchased PurchaseAmount
      2018-01 100
      2018-02 100
      2018-03 100


      How to accomplish this? Is there a better way than creating a separate calculated table to get this data? Any help or advise is appreciated










      share|improve this question
















      I have a table in a Power BI data source with a column for term start and term end date (term length can be longer than a month), along with meta data on the term. I need to report on status of purchased terms as at the end of each month. As far as I can see, the best way of accomplishing this would be to create a calculated table with an entry for each month on which a term is active at its end.



      For example, an entry in the original table with the following data:



      TermStartDate TermEndDate PurchaseAmount
      2018-01-03 2018-04-12 100


      Would end up in the calculated table as follows:



      MonthPurchased PurchaseAmount
      2018-01 100
      2018-02 100
      2018-03 100


      How to accomplish this? Is there a better way than creating a separate calculated table to get this data? Any help or advise is appreciated







      powerbi dax m






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 26 '18 at 9:32







      BOENDAGGER

















      asked Nov 23 '18 at 13:41









      BOENDAGGERBOENDAGGER

      143317




      143317
























          1 Answer
          1






          active

          oldest

          votes


















          0














          I managed to solve this myself, I detail the required steps below for reference:




          1. Change start and end date column data types from Datetime to Date. <- This is needed to ensure we only generate dates on day boundaries in the next step


          2. Add custom column with the following formula:



            Month = List.Select( List.Dates([TermStartDate], Number.From([TermEndDate] - [TermStartDate]) +1, #duration(1, 0, 0, 0)), each _ = Date.EndOfMonth(_) )



            This generates a list of dates between start and end, then filters to only leave the dates that are at the end of a month



          3. Expand to new rows on the new Month column (menu at the top of the column)


          4. Use Detect Data Type option on the Month column to change the datatype from Any to Date (for some reason I cannot manually select Date, the DataType menu option is greyed out on the Month column)







          share|improve this answer























            Your Answer






            StackExchange.ifUsing("editor", function () {
            StackExchange.using("externalEditor", function () {
            StackExchange.using("snippets", function () {
            StackExchange.snippets.init();
            });
            });
            }, "code-snippets");

            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "1"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53447814%2fsplit-single-row-of-data-in-power-bi-data-source-into-multiple-rows%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            0














            I managed to solve this myself, I detail the required steps below for reference:




            1. Change start and end date column data types from Datetime to Date. <- This is needed to ensure we only generate dates on day boundaries in the next step


            2. Add custom column with the following formula:



              Month = List.Select( List.Dates([TermStartDate], Number.From([TermEndDate] - [TermStartDate]) +1, #duration(1, 0, 0, 0)), each _ = Date.EndOfMonth(_) )



              This generates a list of dates between start and end, then filters to only leave the dates that are at the end of a month



            3. Expand to new rows on the new Month column (menu at the top of the column)


            4. Use Detect Data Type option on the Month column to change the datatype from Any to Date (for some reason I cannot manually select Date, the DataType menu option is greyed out on the Month column)







            share|improve this answer




























              0














              I managed to solve this myself, I detail the required steps below for reference:




              1. Change start and end date column data types from Datetime to Date. <- This is needed to ensure we only generate dates on day boundaries in the next step


              2. Add custom column with the following formula:



                Month = List.Select( List.Dates([TermStartDate], Number.From([TermEndDate] - [TermStartDate]) +1, #duration(1, 0, 0, 0)), each _ = Date.EndOfMonth(_) )



                This generates a list of dates between start and end, then filters to only leave the dates that are at the end of a month



              3. Expand to new rows on the new Month column (menu at the top of the column)


              4. Use Detect Data Type option on the Month column to change the datatype from Any to Date (for some reason I cannot manually select Date, the DataType menu option is greyed out on the Month column)







              share|improve this answer


























                0












                0








                0







                I managed to solve this myself, I detail the required steps below for reference:




                1. Change start and end date column data types from Datetime to Date. <- This is needed to ensure we only generate dates on day boundaries in the next step


                2. Add custom column with the following formula:



                  Month = List.Select( List.Dates([TermStartDate], Number.From([TermEndDate] - [TermStartDate]) +1, #duration(1, 0, 0, 0)), each _ = Date.EndOfMonth(_) )



                  This generates a list of dates between start and end, then filters to only leave the dates that are at the end of a month



                3. Expand to new rows on the new Month column (menu at the top of the column)


                4. Use Detect Data Type option on the Month column to change the datatype from Any to Date (for some reason I cannot manually select Date, the DataType menu option is greyed out on the Month column)







                share|improve this answer













                I managed to solve this myself, I detail the required steps below for reference:




                1. Change start and end date column data types from Datetime to Date. <- This is needed to ensure we only generate dates on day boundaries in the next step


                2. Add custom column with the following formula:



                  Month = List.Select( List.Dates([TermStartDate], Number.From([TermEndDate] - [TermStartDate]) +1, #duration(1, 0, 0, 0)), each _ = Date.EndOfMonth(_) )



                  This generates a list of dates between start and end, then filters to only leave the dates that are at the end of a month



                3. Expand to new rows on the new Month column (menu at the top of the column)


                4. Use Detect Data Type option on the Month column to change the datatype from Any to Date (for some reason I cannot manually select Date, the DataType menu option is greyed out on the Month column)








                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Nov 27 '18 at 10:15









                BOENDAGGERBOENDAGGER

                143317




                143317






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Stack Overflow!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53447814%2fsplit-single-row-of-data-in-power-bi-data-source-into-multiple-rows%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    Berounka

                    Sphinx de Gizeh

                    Different font size/position of beamer's navigation symbols template's content depending on regular/plain...