Forum Discussion

CherC's avatar
CherC
Frequent Visitor
2 years ago

Select measure based on parameter selection

I have a parameter I created to enable a dynamic date axis on a matrix table.  This allows the user to select the date intervals, (for a date range selected elsewhere), which shows the intervals as t...
OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
2 years ago

Hi CherC 

Yes, you can create a "Period-over-Period"  measure that is conditional on the Axis selection.

 

Here are some examples:

1. If you have already defined measures Year/Year % etc:

 

 

Sales Period-over-Period % =
VAR AxisSelection =
    SELECTCOLUMNS ( 'Axis', 'Axis'[Axis] )
VAR Result =
    IF (
        COUNTROWS ( AxisSelection ) = 1, -- Single parameter selection
        SWITCH (
            AxisSelection,
            "Year", [Year/Year %],
            "Quarter", [Quarter/Quarter %],
            "Month", [Month/Month %],
            "Week", [Week/Week %]
        )
    )
RETURN
    Result

 

 

 

2. If you want to include the calcs all within a single measure (assuming the underlying measure is Sales) :

 

 

Sales Period-over-Period % =
VAR AxisSelection =
    SELECTCOLUMNS ( 'Axis', 'Axis'[Axis] )
VAR Result =
    IF (
        COUNTROWS ( AxisSelection ) = 1, -- Single parameter selection
        VAR ValueCurrentPeriod = [Sales]
        VAR ValuePreviousPeriod =
            SWITCH (
                AxisSelection,
                "Year", CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( 'dates'[Date] ) ),
                "Quarter", CALCULATE ( [Sales], DATEADD ( 'dates'[Date], -1, QUARTER ) ),
                "Month", CALCULATE ( [Sales], DATEADD ( 'dates'[Date], -1, MONTH ) ),
                "Week", CALCULATE ( [Sales], DATEADD ( 'dates'[Date], -7, DAY ) )
            )
        RETURN
            DIVIDE ( ValueCurrentPeriod - ValuePreviousPeriod, ValuePreviousPeriod )
    )
RETURN
    Result

 

 

 

Does something like this work for you?