Forum Discussion

3508026's avatar
3508026
Helper II
11 months ago
Solved

Quarter over Quarter calculation - Display Quarters missing

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()
)
Need Help

9 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    11 months ago

    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's avatar
      3508026
      Helper II
      11 months ago

      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  

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    11 months ago

    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.

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    11 months ago

    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's avatar
    v-nmadadi-msft
    Community Support
    11 months ago

    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