Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Problem Suming Months into Quarters

Hello, I have the following measure that only returns the amount of the measure [Value] starting from the previous month.

 

VAR KEY =
    YEAR ( TODAY () ) * 10000
        + MONTH ( TODAY () ) * 100 - 100
RETURN
    IF (
        KEY
            > MAX ( 'D_Calendar'[Year] ) * 10000
                + MAX ( 'D_Calendar'[Month Number] ) * 100,
       0,
        [Value])

 

We are now in October, so the measure only shows data beginning from September onwards.

 

In PBI, we have a matrix that shows the data in the following hierarchy  Year --> Quarter --> Month. For Month is works but for Quarter it doesn't give the correct sum, I understand the problem and why it returns a bigger value than it should be (3000 instead of 1000 for ex) and I replaced MAX with selectedvalue, it also worked for the months but for quarters it returns 0.

 

How can I modify the measure to show monthly values if we drill to months and the correct sum if we go up to Quarters.

 

Thanks in advance

  • Hi, Anonymous 

    Plesase add another measure like:

    Measure 2 = SUMX(VALUES(D_Calendar[Date]),[Your original measure])

    If it doesn't work, please share a sample file for further research.

     

    Best Regards,
    Community Support Team _ Eason

1 Reply

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support

    Hi, Anonymous 

    Plesase add another measure like:

    Measure 2 = SUMX(VALUES(D_Calendar[Date]),[Your original measure])

    If it doesn't work, please share a sample file for further research.

     

    Best Regards,
    Community Support Team _ Eason