Forum Discussion

MarkusBi's avatar
MarkusBi
New Member
7 years ago

Create Dynamic helper table with weeknumber and explicit months

Hi, 

i want to analyze my sql-based data with Power Bi.

I need a dynamic helper list to analyze my data and sum values in calendar weeks AND explicit months.

 

I found a solution to generate a helper list that has date values every 7 days.

With this dynamic table i can create a list with correct weeknumbers.

i´m using this m-statement to generate the dynamic list:

 

= List.Dates(Startdatum, Number.From(DateTime.LocalNow())- Number.From(Startdatum) ,#duration(7,0,0,0))

 

i get a list like this after i have added several custom colums to the initial date like week-no, month, enddate:

startdate 02.01.2017, week 2, month 1, enddate 08.01.2017
startdate 09.01.2017, week 3, month 1, enddate 15.01.2017
startdate 16.01.2017, week 4, month 1, enddate 22.01.2017
startdate 23.01.2017, week 5, month 1, enddate 29.01.2017
startdate 30.01.2017, week 6, month 2, enddate 05.02.2017
startdate 06.02.2017, week 7, month 2, enddate 12.02.2017

 

how can i generate a dynamic list that has explicit months like this with an m-statement?

 

startdate 02.01.2017, week 2, month 1, enddate 08.01.2017
startdate 09.01.2017, week 3, month 1, enddate 15.01.2017
startdate 16.01.2017, week 4, month 1, enddate 22.01.2017
startdate 23.01.2017, week 5, month 1, enddate 29.01.2017
startdate 30.01.2017, week 6, month 1, enddate 31.01.2017
startdate 01.02.2017, week 6, month 2, enddate 05.02.2017
startdate 06.02.2017, week 7, month 2, enddate 12.02.2017

3 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee

    Hi MarkusBi ,

    Could you please tell me the "Startdatum" is a column or a parameter  in your data? And could you please post your desired result if possible? 

     

    Regards,

    Daniel He

    • MarkusBi's avatar
      MarkusBi
      New Member

      Hi Daniel,

       

      Startdatum is a parameter that holds the first date i like to use in the generated list.

      In this case "Startdatum" is a paramter as date with "1.1.2017" as value.

       

      Regards,

      Markus

    • MarkusBi's avatar
      MarkusBi
      New Member

      Hi v-danhe-msft 

       

      do you think it is possible to find a solution for my helpertable-Problem?

       

      Regards,

      M