Rolling calculation for filtered data

[momihaila]

Hi all,

I am fairly new to Power BI and I want to build a NPS dashboard. I have the detailed data at questionnaire level and I am struggling to get rolling kpi's.

I managed to get 2Months rolling count of number of respondents with this:

aa rol1 = CALCULATE(COUNT(CallCenter_Interact[INT ID]), DATESINPERIOD(CallCenter_Interact[End of Month]

However, if I want to calculated the 2M rolling number of Promoters the formula only returns the total for each month :

aa prom roll1 = CALCULATE(COUNT(CallCenter_Interact[INT ID]),FILTER(CallCenter_Interact, CallCenter_Interact[NPS Segment]="Promoter")

See below he results I am currently getting. How am I supposed to filter the kpi and still get 2M rolling?

[Eric_Zhang]

@momihaila

What if you put the same condition to the second measure?

aa prom roll1 =
CALCULATE (
    COUNT ( CallCenter_Interact[INT ID] ),
    FILTER ( CallCenter_Interact, CallCenter_Interact[NPS Segmnt] = "Promoter" ),
    DATESINPERIOD condition in your first measure here
)

If it is not your case, please post some sample data and the expected output.

[momihaila]

@Eric_Zhang

I just realised I didn't paste it all, I already had the same condition for the second measure but still didn't work.

So, the formulas I currently have are:

aa rol1 = CALCULATE(COUNT(CallCenter_Interact[INT ID]), DATESINPERIOD(CallCenter_Interact[End of Month], LASTDATE(CallCenter_Interact[End of Month]),-2, Month)) - this one is working

aa prom roll1 = CALCULATE(COUNT(CallCenter_Interact[INT ID]),FILTER(CallCenter_Interact, CallCenter_Interact[NPS Segment]="Promoter"), DATESINPERIOD(CallCenter_Interact[End of Month], LASTDATE(CallCenter_Interact[End of Month]),-2, Month)) - this one doesn't return 2M rolling

This is what I should get:

EOM	Responders	aa rol1	Promoters	aa prom roll1
31-Jul-17	8	8	5	5
31-Aug-17	7	15	4	9
30-Aug-17	5	12	2	6

This is a sample of the data:

INT ID	End of Month	NPS Segment	Data apel
6034772983	9/30/2017 0:00	Promoter	9/27/2017 0:00
6034765749	9/30/2017 0:00	Promoter	9/27/2017 0:00
6030817892	8/31/2017 0:00	Promoter	8/9/2017 0:00
6030817439	8/31/2017 0:00	Promoter	8/9/2017 0:00
6030808742	8/31/2017 0:00	Promoter	8/9/2017 0:00
6030751814	8/31/2017 0:00	Promoter	8/9/2017 0:00
6030633476	7/31/2017 0:00	Promoter	7/5/2017 0:00
6030631667	7/31/2017 0:00	Promoter	7/28/2017 0:00
6030629317	7/31/2017 0:00	Promoter	7/5/2017 0:00
6030628449	7/31/2017 0:00	Promoter	7/5/2017 0:00
6030626989	7/31/2017 0:00	Promoter	7/7/2017 0:00
6034554329	9/30/2017 0:00	Detractor	9/27/2017 0:00
6034540148	9/30/2017 0:00	Detractor	9/27/2017 0:00
6031061898	8/31/2017 0:00	Detractor	8/23/2017 0:00
6030692102	7/31/2017 0:00	Detractor	7/20/2017 0:00
6034795795	9/30/2017 0:00	Neutral	9/27/2017 0:00
6031116113	8/31/2017 0:00	Neutral	8/23/2017 0:00
6031106167	8/31/2017 0:00	Neutral	8/23/2017 0:00
6030636975	7/31/2017 0:00	Neutral	7/20/2017 0:00
6030631479	7/31/2017 0:00	Neutral	7/26/2017 0:00

Thanks!

[Ashish_Mathur]

Hi @momihaila,

You may refer to my solution here.

Hope this helps.

[Eric_Zhang · Accepted solution]

@momihaila

Thanks for posting those details, which makes your requirement crystal clear. You can try a measure as

aa prom roll1 =
CALCULATE (
    COUNTAX (
        FILTER ( CallCenter_Interact, CallCenter_Interact[NPS Segment] = "Promoter" ),
        CallCenter_Interact[INT ID]
    ),
    DATESINPERIOD (
        CallCenter_Interact[End of Month],
        LASTDATE ( CallCenter_Interact[End of Month] ),
        -2,
        MONTH
    )
)