DAX, Disaggregate PLAN data - start date, end date

[Martin-Prague]

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

[Martin-Prague]

Thank you so much for your help! I learnt the new trick with the List.

[Vvelarde · Accepted solution]

@Martin-Prague

Hi, Please check this PBIX Here

In Query Editor .

 

Let me know if works

Regards

Victor

[bbqq021]

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

[Abduvali]

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

[Martin-Prague]

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,
Martin

[parry2k]

@Martin-Prague to confirm based on your data set, plan for each row will be 365 days, correct?

[Martin-Prague]

Hello, no the period is sometimes different, unfortunately. Start date and End date needs to be taken into calculation.

[parry2k]

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.

[Martin-Prague]

In my sample, yes. The difference should be 365. But this needs to be dynamic.

When i have Plan only for 1.1.2017 - 15.1.2015 then it shoud be recalculated by 15 for this campaign. And my dataset has also these shorter periods.

[parry2k]

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

[Martin-Prague]

Exactly, it could be only DAX measure - and then somehow link it to date dim. Or could be Power query script.

[parry2k]

done, here is the link, look at plan table.

[Martin-Prague]

Perfect, thanks A lot. I will check tommorow, i have now only mobile and Mac. Thanks!!