I need to add runnng total for each month using measure.

[Anonymous]

I have created this data using calculated measure and know I want to measure sum for each running month For rep_month.

example :

for 1st column 2nd column and 3rd measure 

For Jan I need sum of all jan 

feb sum of jan+ feb

march- sum of jan+feb+march

april= sum of jan+feb+march+april

[v-shex-msft]

Hi @Anonymous ,

It is hard to coding formula without any detail information, please share some sample data for test.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[Anonymous]

Here is the sample data for creating a measure. From this data, I have created a measure for each Rollup location and NPSCAT

I have used below formula for creating measure.

promoter Measure = COUNTROWS(FILTER('all data','all data'[Rollup]="EMEALA" && 'all data'[NPSCat] = "Promoter"))

Decorate Measure = COUNTROWS(FILTER('all data','all data'[Rollup]="EMEALA" && 'all data'[NPSCat] = "Detractor"))

 Passive Measure = COUNTROWS(FILTER('all data','all data'[Rollup]="EMEALA" && 'all data'[NPSCat]="Passive"))

from the above measures, I need to calculate the sum of three measures for every 3 consecutive months.

Here is the measure I'm using to calculate for every 3 consecutive months 

_Measure = IF(MIN('all data'[Month])>=5,CALCULATE([promoter Measure]+[Passive Measure]+[Decorate Measure],FILTER(ALL('all data'),'all data'[Rep_Date]<=MAX('all data'[Rep_Date])&& MONTH('all data'[Rep_Date])>=MONTH(MAX('all data'[Rep_Date]))-3),CALCULATE([promoter Measure]+[Passive Measure]+[Decorate Measure],FILTER(ALL('all data'),'all data'[Rep_Date]<=MAX('all data'[Rep_Date])))))

Please let me know the solution.

[v-shex-msft]

Hi @Anonymous ,

You can try to use following measure formula:

_Measure =
CALCULATE (
    [promoter Measure] + [Passive Measure] + [Decorate Measure],
    FILTER (
        ALLSELECTED ( 'all data' ),
        'all data'[Rep_Date] <= MAX ( 'all data'[Rep_Date] )
            && INT ( MONTH ( 'all data'[Rep_Date] ) / 3 )
                + IF ( MOD ( MONTH ( 'all data'[Rep_Date] ), 3 ) > 0, 1, 0 )
                = INT ( MONTH ( MAX ( 'all data'[Rep_Date] ) ) / 3 )
                    + IF ( MOD ( MONTH ( MAX ( 'all data'[Rep_Date] ) ), 3 ) > 0, 1, 0 )
    )
)

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft ,

I need to add 3 consecutive months from the above data. I have to calculate the \

jan = jan

feb = Jan+feb

march = jan+feb+march

april = feb+march+april

may = march+april+may...... so on upto december 

I'm new to power bi. please help me out for the above measure.

[v-shex-msft]

HI @Anonymous ,

Please try to use following measure if it works, I modify conditions to summary rolling three month records:

_Measure =
CALCULATE (
    [promoter Measure] + [Passive Measure] + [Decorate Measure],
    FILTER (
        ALLSELECTED ( 'all data' ),
        'all data'[Rep_Date] <= MAX ( 'all data'[Rep_Date] )
            && MONTH ( 'all data'[Rep_Date] )
                >= MONTH ( MAX ( 'all data'[Rep_Date] ) ) - 2
            && MONTH ( 'all data'[Rep_Date] ) <= MONTH ( MAX ( 'all data'[Rep_Date] ) )
    )
)

Regards,

Xiaoxin Sheng