Forum Discussion
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
Microsoft 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
- MarkusBiNew 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
- MarkusBiNew Member
Hi v-danhe-msft
do you think it is possible to find a solution for my helpertable-Problem?
Regards,
M