Quarter over Quarter calculation - Display Quarters missing

[3508026]

Good morning,

I'm using this calculation to obtain the %churn QoQ.
I have two columns ROLLING_Q which shows the quarters and SEG_MONTH which shows the end of the months of ROLLING_Q.

But the %Churn QoQ calculation disappears if I use ROLLING_Q in the visual elements.
Could you help me solve this, as I want to use the ROLLING_Q field instead of SEG_MONTH?

%Churn_QoQ =

VAR CurrentMonth =

    CALCULATE(

        MAX(DATA[SEG_MONTH]),

        VALUES(DATA[ROLLING_Q])

    )

VAR PrevMonth = EDATE(CurrentMonth, -3)

VAR LostDecline_Current =

    CALCULATE(

        SUM(DATA[Total Customers]),

        DATA[BAT_STATUS] IN { "Lost", "Decline" },

        DATA[SEG_MONTH] = CurrentMonth

    )

VAR LostDecline_Prev =

    CALCULATE(

        SUM(DATA[Total Customers]),

        DATA[BAT_STATUS] IN { "Lost", "Decline" },

        DATA[SEG_MONTH] = PrevMonth

    )

RETURN

IF(

    NOT ISBLANK(LostDecline_Prev),

    DIVIDE(LostDecline_Current - LostDecline_Prev, LostDecline_Prev),

    BLANK()

)

[v-nmadadi-msft]

Hi @3508026 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the suggestions  provided by the community members for the issue worked. Please feel free to contact us if you have any further questions.

Thanks and regards

[v-nmadadi-msft]

Hi @3508026 

May I check if this issue has been resolved? If not, Please feel free to accept the request we raised to access your data.

Thank you

[v-nmadadi-msft]

Hi @3508026 ,

I wanted to check if you had the opportunity to check the access requests me and the community members raised to reproduce your exact scenario. Please feel free to contact us if you have any further questions.

Thank you.

[3508026]

Hi @Ashish_Mathur 
Here is the link: PBI_Rolling_Q 
I appreciate your help

[Ashish_Mathur]

Access Denied message.

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

One of ways is to create a separate calendar table and create a relationship with the fact table.

And, I tried to use OFFSET DAX function in the measure.

OFFSET function (DAX) - DAX | Microsoft Learn

Lost Decline customers count: = 
CALCULATE (
    SUM ( data[customers_count] ),
    'status'[bat_status] IN { "lost", "decline" }
)

prev: = 
CALCULATE (
    [Lost Decline customers count:],
    OFFSET (
        -1,
        ALL ( 'calendar'[Rolling_Q], 'calendar'[SEG_MONTH] ),
        ORDERBY ( 'calendar'[SEG_MONTH], ASC )
    )
)

QoQ: = 
DIVIDE([Lost Decline customers count:] - [prev:], [prev:])

[3508026]

Thank you @Jihwan_Kim 
My table does not contain a date field to display the months included in ROLLING_Q; the only field related to a date is SEG_MONTH. I previously created a table to assign those two and create a relationship so that I could use ROLLING_Q, but it did not work.
I have shared the file; could you take a look at it?. Here is the link:
PBI_Rolling_Q  

[Jihwan_Kim]

[Ashish_Mathur · Accepted solution]

Hi,

Share the download link of the PBI file.