Forum Discussion

lesterhsu's avatar
lesterhsu
Regular Visitor
3 years ago
Solved

Period-over-Period calculation after using Slicers

Hi everyone,

 

I would like to know how to calculate and display a specific period-over-period in a Card visual after using Slicers to select a specific period (e.g., 2023/2023Q2/202306).

 

Here is the sample data. (The data is generated on a monthly basis.)

 

data_daterevenue
2023/1/1285
2023/2/1480
2023/3/1820
2023/4/1180
2023/5/1420
2023/6/11028

 

Here are the scenarios:

  1. By using a Slicer to select 2023/6, the Card visual will show the MoM revenue percentage.

  2. By using a Slicer to select 2023/4-6, the Card visual will show the QoQ revenue percentage.

  3. Only use a Card visual.

Any advice would be appreciated. Thank you.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi lesterhsu ,

     

    You could try creating a calendar table and a measure to achieve.

    Here's the solution.

    1.Calendar table:

    Calendar = ADDCOLUMNS(CALENDAR(DATE(2023,1,1),DATE(2023,12,31)),"YearMonth",FORMAT([Date],"YYYYMM"))

    There's no relationship between tables.

    2.Create a measure to calcualte the percentage.

    Percentage = VAR _COUNT=DISTINCTCOUNT('Calendar'[YearMonth])
VAR _MIN=MIN('Calendar'[Date])
VAR _MAX=MAX('Calendar'[Date])
VAR _PREMIN=EOMONTH(_MIN,-_COUNT-1)+1
VAR _PREMAX=EOMONTH(_MAX,-_COUNT)
VAR _SEL=CALCULATE(SUM('Table'[revenue]),FILTER(ALLSELECTED('Table'),[data_date]>=_MIN&&[data_date]<=_MAX))
VAR _PRE=CALCULATE(SUM('Table'[revenue]),FILTER(ALLSELECTED('Table'),[data_date]>=_PREMIN&&[data_date]<=_PREMAX))
RETURN DIVIDE(_SEL,_PRE)

     

                                                                                                                                                             

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

5 Replies

  • grazitti_sapna's avatar
    grazitti_sapna
    Icon for Super User rankSuper User
    3 years ago

    Hi lesterhsu 

    You can try using:-

    For Selected period

    SelectedPeriodRevenue =
    VAR SelectedDate = SELECTEDVALUE('Table'[data_date])
    RETURN
    CALCULATE(
    SUM('Table'[revenue]),
    FILTER('Table', 'Table'[data_date] = SelectedDate)
    )

     

    For MoM

    MoMRevenuePercentage =
    VAR SelectedDate = SELECTEDVALUE('Table'[data_date])
    VAR PreviousMonthDate = EOMONTH(SelectedDate, -1)
    VAR SelectedRevenue = [SelectedPeriodRevenue]
    VAR PreviousMonthRevenue =
    CALCULATE(
    [SelectedPeriodRevenue],
    ALL('Table'[data_date]),
    'Table'[data_date] = PreviousMonthDate
    )
    RETURN
    IF(ISBLANK(PreviousMonthRevenue), BLANK(), DIVIDE(SelectedRevenue - PreviousMonthRevenue, PreviousMonthRevenue))

     

    For QoQ

    QoQRevenuePercentage =
    VAR SelectedDate = SELECTEDVALUE('Table'[data_date])
    VAR PreviousQuarterDate = EOMONTH(SelectedDate, -3)
    VAR SelectedRevenue = [SelectedPeriodRevenue]
    VAR PreviousQuarterRevenue =
    CALCULATE(
    [SelectedPeriodRevenue],
    ALL('Table'[data_date]),
    'Table'[data_date] = PreviousQuarterDate
    )
    RETURN
    IF(ISBLANK(PreviousQuarterRevenue), BLANK(), DIVIDE(SelectedRevenue - PreviousQuarterRevenue, PreviousQuarterRevenue))

    • lesterhsu's avatar
      lesterhsu
      Regular Visitor
      3 years ago

      Hi grazitti_sapna 

      Thank you for the reply, but it didn't work when I put MoM/QoQ measures into Card visual.

      Also, I need to display MoM/QoQ at the same Card visual, any suggestions are welcomed.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi lesterhsu ,

     

    You could try creating a calendar table and a measure to achieve.

    Here's the solution.

    1.Calendar table:

    Calendar = ADDCOLUMNS(CALENDAR(DATE(2023,1,1),DATE(2023,12,31)),"YearMonth",FORMAT([Date],"YYYYMM"))

    There's no relationship between tables.

    2.Create a measure to calcualte the percentage.

    Percentage = VAR _COUNT=DISTINCTCOUNT('Calendar'[YearMonth])
VAR _MIN=MIN('Calendar'[Date])
VAR _MAX=MAX('Calendar'[Date])
VAR _PREMIN=EOMONTH(_MIN,-_COUNT-1)+1
VAR _PREMAX=EOMONTH(_MAX,-_COUNT)
VAR _SEL=CALCULATE(SUM('Table'[revenue]),FILTER(ALLSELECTED('Table'),[data_date]>=_MIN&&[data_date]<=_MAX))
VAR _PRE=CALCULATE(SUM('Table'[revenue]),FILTER(ALLSELECTED('Table'),[data_date]>=_PREMIN&&[data_date]<=_PREMAX))
RETURN DIVIDE(_SEL,_PRE)

     

                                                                                                                                                             

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

    • lesterhsu's avatar
      lesterhsu
      Regular Visitor
      3 years ago

      Hi Anonymous 

      Thank you for the solution, it works! However, I have another question.

      The calendar slicer didn't apply to other visuals and measures. For example, some measures use "data_date" to calculate. Is there any way to solve this problem? Thank you!

  • Prateek0106's avatar
    Prateek0106
    New Member
    3 years ago

    Whenever I switch my toggle in the Power BI Dashboard, the slicers gets reset every single time, how can i overcome this problem, any solutions, if any please reach me out to me at.

     

    [email protected]

     

     

    With Regards

    Prateek Singh