cancel
Showing results for
Did you mean:
Frequent Visitor

## Date Range daily values to Monthly totals

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

 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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Date Range daily values to Monthly totals

Hi @njoaquim,

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] )
)```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Microsoft

## Re: Date Range daily values to Monthly totals

Hi @njoaquim,

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] )
)```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Date Range daily values to Monthly totals

Thank You Yuliana, this worked great

Nuno

Announcements

#### ‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors