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

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.