Forum Discussion

peter_rips's avatar
peter_rips
Frequent Visitor
9 years ago
Solved

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

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

Need Help
  • peter_rips's avatar
    peter_rips
    9 years ago

    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

2 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    9 years ago

    peter_rips,

     

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

    • peter_rips's avatar
      peter_rips
      Frequent Visitor
      9 years ago

      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