Period-over-Period calculation after using Slicers

[lesterhsu]

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_date	revenue
2023/1/1	285
2023/2/1	480
2023/3/1	820
2023/4/1	180
2023/5/1	420
2023/6/1	1028

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.

[Prateek0106]

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.

prateek.singh@ennvee.net

With Regards

Prateek Singh

[Anonymous · Accepted solution]

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]

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!

[grazitti_sapna]

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]

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.