Normalize monthly values based on 1st month SUM/total values

[peter_rips]

Hello there,

I'm stuck at the moment, so I hope someone can give me some light at this stage regarding this subject.

I've been trying to normalize my data, using the following example (extracted from a similar problem)

1. Define a measure [Value First Date]
   

   Value First Date =
   /* Optional check: Only return Value First Date up to the max date that actually appears in the fact table */
   IF (
       MIN ( 'Calendar'[Date] ) <= CALCULATE ( MAX ( Data[Date] ), ALL ( Data ) ),
       CALCULATE (
           [Value],
           CALCULATETABLE ( FIRSTDATE ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
       )
   )

2. Define a [Value Normalized] measure:
   

   Value Normalized =
   DIVIDE ( [Value], [Value First Date] ) * 100

This example works great/perfect if I want to normalize my data based on the 1st day of the 1st month.

This example will grab "200" from the following table, but what I need here is to get "900" and do exactly the same as the rest of the example code. 

DataDate Value

1-01-2017  200 

3-01-2017  400

7-01-2017  300

My Goal Here is to do exactly the same, but using the sum of all values in the 1st month, instead of the 1st value (firstday) only.

The calculation is done then by dividing each of the values of following months by the total value of the first month(900), and multiplying by 100.

So, I'm starting my DAX now, so apologies if this is too easy.

Thanks in advance.

Peter

[v-chuncz-msft]

@peter_rips,

The example above is not very clear. It would be better if you could share us your simplified .pbix file.

[peter_rips · Accepted solution]

Hi @v-chuncz-msft, thanks for coming back to me.

It´s not easy since I have a lot of confidential data and several metrics.

Anyway, hopefully, I managed to find a solution for this, the following way:

1- 

IndexR2 = 
CALCULATE (
        sum(Facts[Sales_U]), 
        CALCULATETABLE ( DATESBETWEEN('Date'[DateFullName],STARTOFMONTH('Date'[DateFullName]),ENDOFMONTH(STARTOFMONTH('Date'[DateFullName]))), ALLSELECTED ( 'Date' )
    )
)

2-

IndexR2N = DIVIDE ( Calculate(Sum(Facts[Sales_U])), [IndexR2] ) * 100

and that's it.

Thanks anyway, and sorry case I was not clear and able to share the .pbix

Peter