Get correct value once spliting starting date and ending date

[gustavop]

Hi there,

Appreciate if someone help to fix my issue.

I have a report with start date and end date collumn and other collumn with sales values. Once I place the values per month it doesnt give it correct since the value has been allocated into the end date pool.

Eg: Start date 20.12.2020 End date 03.12.2020. Sales 1000

The graphic place the 1000 into the end date so the 1000k will be placed for December.

In this case, I would need the value be placed proportionally correct when comes to split the date value correct in each month in my graphic.

Tks for 

[v-shex-msft]

Hi @gustavop,

Can please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[gustavop]

Hi there,

Tks for getting back to my issue.

The point is, I have the start date, end date and retail sales values.

Once I got the start date 20 dec 2020 and 3 jan 2021 and retail value 20k and put it into the graphic, all the 20 k goes to end date so it is displayed into jan month.

What I need here to have the total retail 20k split equally all over all dates in this range 20 dec 2020 and 3 jan 2021. Like below:

20 dec 2020 to 3 jan 2021 = 14 days

Retail sales = 20k

20k/14 = 1.428

So it will be

20 dec 2020 = 1,428

21 dec 2020 = 1,428

.

.

.

1 Jan 2021= 1,428

So like this once placed in the graphic I will have the lines closed to reality. It means I will have value in dec and jan.

Tks for your help 😉

[v-shex-msft · Accepted solution]

Hi @gustavop,

You can take a look at the following link to create a calculated table to expand the date ranges between start end date.
After these steps, you can filter on the new table date field to simply interact/summary with raw table records based on filter selections.

Spread revenue across period based on start and end date, slice and dase this using different dates 
Regards,

Xiaoxin Sheng

[amitchandak]

@gustavop , check if my blog on this can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...