Date Range daily values to Monthly totals

[njoaquim]

I have a table that has a customer, a date range, and Daily Margin, similar to table below

Customer	StartDate	EndDate	DailyMargin
Customer1	Jan-1-2016	July-31-2016	1
Customer2	Jan-1-2016	March-31-2016	3
Customer3	Feb-1-2016	March-31-2016	2
Customer1	April-1-2016	May-31-2016	1.5

I want to be able to get a Monthly Margin, Yearly Margin, by customer or as a whole

Thanks in advance for your help,

Customer	Month	MonthMargin
Customer1	Jan-2016	31
Customer1	Feb-2016	29
Customer1	Mar-2016	31
Customer1	Apr-2016	75	Daily Margin 1 + 1.5
Customer1	May-2016	77.5	Daily Margin 1 + 1.5
Customer1	Jun-2016	30
Customer1	Jul-2016	31
Customer2	Jan-2016	93
Customer2	Feb-2016	87
Customer2	Mar-2016	93
Customer3	Feb-2016	58
Customer3	Mar-2016	62

[v-yulgu-msft · Accepted solution]

Hi @njoaquim,

Please refer to below steps.

Create calendar table.

Dim =
CALENDAR ( MIN ( MonthMargin[StartDate] ), MAX ( MonthMargin[EndDate] ) )

-----add a calculated column in Dim table
Days in a month =
CALCULATE ( COUNT ( Dim[Date] ), ALLEXCEPT ( Dim, Dim[Date].[MonthNo] ) )

-----filter Dim table
Dim2 = FILTER(Dim,Dim[Date].[Day]=1)

Create a calculated table via CrossJoin source table and dim table. And create two calculated columns.

Table1 =
FILTER (
    CROSSJOIN ( MonthMargin, Dim2 ),
    Dim2[Date] >= MonthMargin[StartDate]
        && Dim2[Date] <= MonthMargin[EndDate]
)

Month Margin 1 =
Table1[Days in a month] * Table1[DailyMargin]

Month = Table1[Date].[Month] & "-" & Table1[Date].[Year]

At last, summarize above table, you can get below output

Table2 =
SUMMARIZE (
    Table1,
    Table1[Customer],
    Table1[Month],
    "MonthMargin", SUM ( Table1[Month Margin 1] )
)

I have uploaded my .pbix file (a test based on your sample data) for your reference.

Best regards,

Yuliana Gu

[njoaquim]

Thank You Yuliana, this worked great

Nuno