Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Solved! Go to Solution.
Thank you so much for your help! I learnt the new trick with the List.
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
Regards
Abduvali
@Martin-Prague to confirm based on your data set, plan for each row will be 365 days, correct?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Understood, but in the sample you mentioned it will be difference between dates, and your sample data will return 365 days, correct?
or you want plan value do be divided by different in number of days.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
are you expecintg this output
1 1/1/2016 365 1111
1 1/2/2016 365 1111
1 1/3/2016 365 1111
1 1/4/2016 365 1111
1 1/5/2016 365 1111
....
so basically each campaign is broken down by date, with date difference between start/end date and plan value,c orrect?
above is just an example
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
done, here is the link, look at plan table.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
218 | |
87 | |
71 | |
63 | |
60 |