cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

Calculation of monthly figures from YTD for serveral groups

Hello all together,

I would be glad if someone could give me the solution. I have read through many many post, but did not find what I was looking for.

I have a table like this and want to calculate the monthly sales by month and business unit.

 Date Business Unit Sales cumm. monthly sales 31.01.2016 ABC 388.286 388.286 28.02.2016 ABC 1.128.132 739.846 31.03.2016 ABC 1.742.896 614.764 30.04.2016 ABC 2.196.541 453.645 31.05.2016 ABC 2.654.779 458.238 30.06.2016 ABC 3.295.551 640.772 31.07.2016 ABC 3.847.267 551.716 31.08.2016 ABC 4.210.714 363.447 30.09.2016 ABC 4.749.356 538.642 31.10.2016 ABC 5.351.004 601.647 30.11.2016 ABC 5.808.109 457.106 31.01.2016 DEF 436.728 436.728 28.02.2016 DEF 924.156 487.428 31.03.2016 DEF 1.512.040 587.884 30.04.2016 DEF 2.173.243 661.202 31.05.2016 DEF 2.629.142 455.899 30.06.2016 DEF 3.036.177 407.035 31.07.2016 DEF 3.774.962 738.785 31.08.2016 DEF 4.340.396 565.434 30.09.2016 DEF 4.885.096 544.700 31.10.2016 DEF 5.796.794 911.698 30.11.2016 DEF 6.598.860 802.066

I guess for the most of you it´s quite simple, but at least I have no idea.

Thank you very much!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Re: Calculation of monthly figures from YTD for serveral groups

Hi @ChrisZ

The solution is to find the cumulated valued upto the previous month and use this value to subtract from current month cumulated value to derive the months value.

The data supplied by you is loaded as table name CumSales. The date column of this table should be set to dd/mm/yyyy format.

Here you go.

1. Create a calendar table using

Calendar = Calendar(Min(CumSales[Date]),max(CumSales[Date]))

Set the column Date to format dd/mm/yyyyy.

1. Create column month in the calendar table

Month = Month([Date])

1. Create column in calendar table

MonthDayNumber = DAY(Calendar[Date])

1. Create column in calendar table

MonthDays = COUNTROWS ( FILTER( 'Calendar',  'Calendar'[YearMonthNumber] = EARLIER ( 'Calendar'[YearMonthNumber] ) ) )

1. Create column in calendar table

Year = YEAR('Calendar'[Date])

1. Create column in calendar table

YearMonthNumber = YEar ([Date]) *12+Month([Date]) – 1

1. Create column in calendar table

PMDate =

CALCULATE (

MAX  ( 'Calendar'[Date] ),

ALL ( 'Calendar' ),

FILTER (

ALL ( 'Calendar'[MonthDayNumber] ),

'Calendar'[MonthDayNumber] <= EARLIER ( 'Calendar'[MonthDayNumber] )

|| EARLIER('Calendar'[MonthDayNumber] ) = EARLIER ( 'Calendar'[MonthDays] )

),

FILTER (

ALL ( 'Calendar'[YearMonthNumber] ),

'Calendar'[YearMonthNumber]

= EARLIER ( 'Calendar'[YearMonthNumber] ) - 1

)  )

1. Define relationship between CumSales[Date] and Calendar[Date] columns.
2. Create measure SalesCum = Sum(CumSales[monthly sales])

I have used monthly sales from your sample data as the **bleep** Sales contains decimal numbers instead of comma and had no time to alter.

For your testing replace this as SalesCum = Sum(CumSales[Sales cumm.])

1. Create measure PMTDSales

PMTD Sales = CALCULATE (

[SalesCum],

FILTER (

ALL ( 'Calendar' ),

'Calendar'[YearMonthNumber] = MAX ( 'Calendar'[YearMonthNumber] ) - 1

&& 'Calendar'[Date] <= MAX ( 'Calendar'[PMDate] )

)

)

1. Create measure MonthsValue

MonthsValue = [SalesCum]-[PMTD Sales]

1. Now plot the Month from Calendar Table, Business Unit, Sales cumm., PMTDSales and

MonthsValue.

1. You should get the desired results.

If this works for you please accept this as solution and also give KUDOS.

Cheers

CheenuSing

Sample screen shot

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
6 REPLIES 6
Super User

Re: Calculation of monthly figures from YTD for serveral groups

Hi @ChrisZ

Simply create a measure called SumSales = sum([Monthly_sales])

Create slicers for business unit .

Create slicers for Date.Month

Plot any graph with SumSales measure as values and Business unit as Axis.

SImilarly plot any graph with SumSales measure as values and Date as Axis.

You will achieve what you want.

If this works for you please accept this as solution and also give KUDOS.

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Frequent Visitor

Re: Calculation of monthly figures from YTD for serveral groups

Hi @CheenuSing,

thank you for your quick reply. I guess my question/description was not precise enough.

I only have in my data table the following columns: Date, BU, Sales YTD per BU

Now I want to create a measure or additonal column in Power BI that calculates the monthly sales of the "corresponding" BU.

The 4th column in my table only shows, what I expect as result of the measure, but is it not existing yet.

Would be great if you can help on this aswell.

Christian

Community Support Team

Re: Calculation of monthly figures from YTD for serveral groups

Hi @ChrisZ,

According to your description, you want to group the data by date and business unit, right?

If this is a case, you can refer to below methods to achieve your requirement.

1. Group with matrix visual.

2. Group with summarize function.

Table = SUMMARIZE(Sheet1,[Format].[MonthNo],[Business Unit],"Sales cumm",SUM(Sheet1[Sales cumm.]),"Monthly Sales",SUM(Sheet1[monthly sales]))

Result:

Regards,

Xiaoxin Sheng

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

For learning resources/Release notes, please visit: | |
Frequent Visitor

Re: Calculation of monthly figures from YTD for serveral groups

Hello @v-shex-msft,

what I need is a DAX Formula / Measure to calculate the monthly sales by Business unit, if I have only the following data table.

 Date Business Unit Sales YTD 31.01.2016 ABC 388.286,40 28.02.2016 ABC 1.128.132,20 31.03.2016 ABC 1.742.896,10 30.04.2016 ABC 2.196.540,98 31.05.2016 ABC 2.654.778,87 30.06.2016 ABC 3.295.550,83 31.07.2016 ABC 3.847.266,86 31.08.2016 ABC 4.210.714,27 30.09.2016 ABC 4.749.356,21 31.10.2016 ABC 5.351.003,68 30.11.2016 ABC 5.808.109,29 31.01.2016 DEF 436.728,00 28.02.2016 DEF 924.156,41 31.03.2016 DEF 1.512.040,40 30.04.2016 DEF 2.173.242,52 31.05.2016 DEF 2.629.141,96 30.06.2016 DEF 3.036.177,26 31.07.2016 DEF 3.774.962,39 31.08.2016 DEF 4.340.396,10 30.09.2016 DEF 4.885.096,11 31.10.2016 DEF 5.796.794,08 30.11.2016 DEF 6.598.859,98

So i.e. the result of this formula should be for ABC in January is 388.286,40; for February it´s 739.845,80 etc.

I hope it is a little bit more understandable. now.

Thanks again!

Christian

Super User

Re: Calculation of monthly figures from YTD for serveral groups

Hi @ChrisZ

The solution is to find the cumulated valued upto the previous month and use this value to subtract from current month cumulated value to derive the months value.

The data supplied by you is loaded as table name CumSales. The date column of this table should be set to dd/mm/yyyy format.

Here you go.

1. Create a calendar table using

Calendar = Calendar(Min(CumSales[Date]),max(CumSales[Date]))

Set the column Date to format dd/mm/yyyyy.

1. Create column month in the calendar table

Month = Month([Date])

1. Create column in calendar table

MonthDayNumber = DAY(Calendar[Date])

1. Create column in calendar table

MonthDays = COUNTROWS ( FILTER( 'Calendar',  'Calendar'[YearMonthNumber] = EARLIER ( 'Calendar'[YearMonthNumber] ) ) )

1. Create column in calendar table

Year = YEAR('Calendar'[Date])

1. Create column in calendar table

YearMonthNumber = YEar ([Date]) *12+Month([Date]) – 1

1. Create column in calendar table

PMDate =

CALCULATE (

MAX  ( 'Calendar'[Date] ),

ALL ( 'Calendar' ),

FILTER (

ALL ( 'Calendar'[MonthDayNumber] ),

'Calendar'[MonthDayNumber] <= EARLIER ( 'Calendar'[MonthDayNumber] )

|| EARLIER('Calendar'[MonthDayNumber] ) = EARLIER ( 'Calendar'[MonthDays] )

),

FILTER (

ALL ( 'Calendar'[YearMonthNumber] ),

'Calendar'[YearMonthNumber]

= EARLIER ( 'Calendar'[YearMonthNumber] ) - 1

)  )

1. Define relationship between CumSales[Date] and Calendar[Date] columns.
2. Create measure SalesCum = Sum(CumSales[monthly sales])

I have used monthly sales from your sample data as the **bleep** Sales contains decimal numbers instead of comma and had no time to alter.

For your testing replace this as SalesCum = Sum(CumSales[Sales cumm.])

1. Create measure PMTDSales

PMTD Sales = CALCULATE (

[SalesCum],

FILTER (

ALL ( 'Calendar' ),

'Calendar'[YearMonthNumber] = MAX ( 'Calendar'[YearMonthNumber] ) - 1

&& 'Calendar'[Date] <= MAX ( 'Calendar'[PMDate] )

)

)

1. Create measure MonthsValue

MonthsValue = [SalesCum]-[PMTD Sales]

1. Now plot the Month from Calendar Table, Business Unit, Sales cumm., PMTDSales and

MonthsValue.

1. You should get the desired results.

If this works for you please accept this as solution and also give KUDOS.

Cheers

CheenuSing

Sample screen shot

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Frequent Visitor

Re: Calculation of monthly figures from YTD for serveral groups

GREAT! it works, thank you very much for your support.

Christian