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.           

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!