Forum Discussion

momihaila's avatar
momihaila
Frequent Visitor
8 years ago
Solved

Rolling calculation for filtered data

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 rol...
  • Eric_Zhang's avatar
    Eric_Zhang
    8 years ago

    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
    )
)

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
8 years ago

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's avatar
momihaila
Frequent Visitor
8 years ago

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:

EOMRespondersaa rol1Promotersaa prom roll1
31-Jul-178855
31-Aug-1771549
30-Aug-1751226

 

This is a sample of the data:

INT IDEnd of MonthNPS SegmentData apel
60347729839/30/2017 0:00Promoter9/27/2017 0:00
60347657499/30/2017 0:00Promoter9/27/2017 0:00
60308178928/31/2017 0:00Promoter8/9/2017 0:00
60308174398/31/2017 0:00Promoter8/9/2017 0:00
60308087428/31/2017 0:00Promoter8/9/2017 0:00
60307518148/31/2017 0:00Promoter8/9/2017 0:00
60306334767/31/2017 0:00Promoter7/5/2017 0:00
60306316677/31/2017 0:00Promoter7/28/2017 0:00
60306293177/31/2017 0:00Promoter7/5/2017 0:00
60306284497/31/2017 0:00Promoter7/5/2017 0:00
60306269897/31/2017 0:00Promoter7/7/2017 0:00
60345543299/30/2017 0:00Detractor9/27/2017 0:00
60345401489/30/2017 0:00Detractor9/27/2017 0:00
60310618988/31/2017 0:00Detractor8/23/2017 0:00
60306921027/31/2017 0:00Detractor7/20/2017 0:00
60347957959/30/2017 0:00Neutral9/27/2017 0:00
60311161138/31/2017 0:00Neutral8/23/2017 0:00
60311061678/31/2017 0:00Neutral8/23/2017 0:00
60306369757/31/2017 0:00Neutral7/20/2017 0:00
60306314797/31/2017 0:00Neutral7/26/2017 0:00

 

 

 

Thanks!

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    8 years ago

    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
    )
)