Forum Discussion
DAX, Disaggregate PLAN data - start date, end date
Hello Comunity,
I have source data in this structure:
Campaign ID Start date End date Plan 888405244 01.01.2016 31.12.2016 726190 888405244 01.01.2017 31.12.2017 890145 873145821 01.01.2016 31.12.2016 726190 873145821 01.01.2017 31.12.2017 890145 873145818 01.01.2016 31.12.2016 726190
I would like to recalculate (disaggregate) this data on weekly / monthly or even better on daily level.
And still be able to filter on campaing ID.
The best scerario that I can image is achive the behaviour like having this structure:
Campaign ID Date Plan 888405244 01.01.2016 1989,561644 888405244 02.01.2016 1989,561644
.....
Plan should be recalculated by Datesbetween (Start Date and End Date). Plan is not always on the whole year.
Is this possible to somehow achieve this with some DAX measure? Or somehow recculated this table in Power Query?
Thanks,
Martin
13 Replies
Hi Vvelarde
Thanks for your solution, it works really well.
I would like to add some additional functionality, instead of spreading a task evenly over the days between the start and end i only want to spread it over week days. My second step would only spread it over working days (excluding bank holidays).
I anticiapte the steps to be:
Count the week days between end and start and divide task resouces by this
Apply this daily quantity to just the week days
Can you give me some pointers on how to achieve this? I do have date tables which include week days or working days however the modifications you are making are in the pre-processing stage. I am not sure how to incorporate them.
Thanks
Will
Hi Martin-Prague,
Just create new quick measure and select Concatenated List of Values
- For more details check the video on my channel: How to create new quick measure for Concatenated List of Values in Power BI
Regards
Abduvali
- Hi Abduvali,
Thank your for your reply, but this is not what I want. My result is not concatenating Plan.
I need to recalculate the data (Plan column) by number of days in period (Between start date and end date). In order to be Able to be plot this plan on daily/weekly... level.
Regards,
MartinMartin-Prague to confirm based on your data set, plan for each row will be 365 days, correct?
Thank you so much for your help! I learnt the new trick with the List.
