Splitting values equally into Months based on the Start and End Date

[Guneet_B21]

Hi,

I'm looking for a solution to split the value of a column equally amongst months based on the Start and the end date columns.
Need to split multiple columns like that and present it in a Matrix Visual

[jppv20]

What does your input data (tables) look like? So I can replicate the problem

[Guneet_B21]

This is the format i have the Input:

and this is kind of what i want to see in the BI:

Hope this is enough to make you understand my issue

[jppv20]

@Guneet_B21 ,

First create a Calendar table like (you can change the dates if you require a longer period):

Calendar = CALENDAR(DATE(2020,1,1),DATE(2021,12,31)) 

Then create a Month column in the calendar table:

Month = FORMAT(DATE(1,MONTH('Calendar'[Date]),1),"MMMM")

Crossjoin your initial table with the calendar table (I called your table organisations):

Table1 = FILTER(CROSSJOIN('Organisations','Calendar'),'Calendar'[Date]>='Organisations'[Start Date]&&'Calendar'[Date]<='Organisations'[End Date])

Then create this measure:

Gross Profit per Month =

var MonthDiff = CALCULATE(DATEDIFF(MIN(Table1[Start Date]),MIN(Table1[End Date]),MONTH))

var GrossProfit = CALCULATE(AVERAGE('Table1'[Gross Profit]),ALLEXCEPT(Table1,Table1[Organisation]))

RETURN

GrossProfit/MonthDiff

And put everything in a matrix:

I put a date filter = this year on the visual and created an extra month column:

Month 2 = MONTH(Table1[Date])

in order to sort Month by Month 2

Hope this helps!

If I answered your question, please mark it as a solution to help other members find it more quickly. 

[Guneet_B21]

The value just seems to be repeating instead of getting divided, even in total i see the same value;

It isnt even divided through the Months, seeing the same value each year,

and in some cases i see infinity

[v-rzhou-msft]

Hi @Guneet_B21 ,

Here I create a same sample data table and an unrelated dax date table to have a test.

data table:

date table:

Calendar = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMMM"))

Measure:

Basic Gross Profit = 
VAR _MONTHSTART =
    MIN ( 'Calendar'[Date] )
VAR _DateDiff =
    DATEDIFF ( MAX ( 'Table'[Start Date] ), MAX ( 'Table'[End Date] ), MONTH )
VAR _AVG =
    DIVIDE ( CALCULATE ( SUM ( 'Table'[Gross Profit] ) ), _DateDiff )
RETURN
    IF (
        _MONTHSTART >= MAX ( 'Table'[Start Date] )
            && _MONTHSTART < MAX ( 'Table'[End Date] ),
        _AVG
    )

Basic Revenue = 
VAR _MONTHSTART =
    MIN ( 'Calendar'[Date] )
VAR _DateDiff =
    DATEDIFF ( MAX ( 'Table'[Start Date] ), MAX ( 'Table'[End Date] ), MONTH )
VAR _AVG =
    DIVIDE ( CALCULATE ( SUM ( 'Table'[Revenue] ) ), _DateDiff )
RETURN
    IF (
        _MONTHSTART >= MAX ( 'Table'[Start Date] )
            && _MONTHSTART < MAX ( 'Table'[End Date] ),
        _AVG
    )

Correct_Gross Profit = 
SUMX(GENERATE(SUMMARIZE('Calendar','Calendar'[Year],'Calendar'[MonthName]),VALUES('Table'[Organization])),[Basic Gross Profit])

Correct_Revenue = 
SUMX(GENERATE(SUMMARIZE('Calendar','Calendar'[Year],'Calendar'[MonthName]),VALUES('Table'[Organization])),[Basic Revenue])

 [Basic Gross Profit] and [Basic Revenue] will show incorrect result in matrix subtotal, so we need to create below two measures to show correct results.

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[a-bekkaoui]

THank you for this example, question though, I managed to get the result but only after breaking the relationship between the fact table and the date table, do you know Why?

Then another question plz, why numbers related to AB and AE don't show up? 

[spider_Monkey]

@v-rzhou-msft So let me ask you this, if I wanted to split this out to cost by day vs month how would I change the formula?

I still want it to show by month, but I want to use the days so things are distrubed accordingly at the front and back end. 

[Guneet_B21]

Hi @v-rzhou-msft ,
Thank you so much for the solution but sadly still not getting the full result,

keep missing few values in months,
not sure why, maybe the data is too big for this type of soltuion

[v-rzhou-msft · Accepted solution]

Hi @Guneet_B21 ,

I think you can sort your MonthName column (text type) by Month column (number type) and check your result agian. You can download my sample file above and compare my data model with yours. Maybe, you can find some difference and update the measure.

My measure logic both [Gross Profit] or [Revenue] are the same.

1. Calculate monthdiff between start date and end date for each [Org.Name].

2. Calculate the avg by (sum[Revenue] or sum[Gross Profit] for each [Org.Name] ) / monthdiff above. At this time all Year Month will show the same and correct avg for each [Org.Name]

3. Add a range to let matrix only show avg in Year Month which is in range.

However the result calculate by this way will show incorrect subtotal or total in matrix. 

So finally, we sumx the avg like correct measure.

If this reply still couldn't help you to solve your problem, please share a sample file without sensitive data with me. And show me a screenshot or a example with the result you want.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Guneet_B21]

Thank you so much @v-rzhou-msft ,

it is now working perfectly

[jppv20]

Hi @Guneet_B21 ,

Please share some example data and expected output

[Guneet_B21]

Eg:
A company had Gross Revenue : 2050000 for year 2021

Start Date of the trade was 01-Jan-21 and the End Date was 01-Oct-21

i want this value to split equally among these 10 months,
i.e. 205000 in each month