Calculation of monthly figures from YTD for serveral groups

[ChrisZ]

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!

[v-shex-msft]

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

[ChrisZ]

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

[CheenuSing · Accepted solution]

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

[ChrisZ]

Hi @CheenuSing

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

Christian

[CheenuSing]

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

[ChrisZ]

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