Forum Discussion

darkmetrics's avatar
darkmetrics
Icon for Helper I rankHelper I
3 years ago
Solved

How to use multiple measures with var syntax in a SWITCH()?

Good afternoon!

I am relatively new to Power BI.
I have a slicer. It is constructed based on DebitSalesDateSlicer table which has only one column [Period] with custom date periods: MTD, YTD, QTD.

 

 

I also have a table with 2 columns: Date and ActiveSales. I want to calculate a measure FirstTablePrevPeriodSales based on slicer value selected by user. This measure returns sum of sales for previous month (PreviousMonthActiveSales) if selected slicer value is MTD, sum of sales for previous quarter (PreviousQuarterActiveSales) if selected slicer value is QTD etc.

The code is below. How can I correct my code to make it work?

 

var PreviousMonthActiveSales := 

var PrevMonthYear = IF(MONTH(TODAY()) == 1, YEAR(TODAY())-1, YEAR(TODAY()))
var PrevMonth = IF(MONTH(TODAY()) == 1, 12, MONTH(TODAY()) - 1)


CALCULATE(
SUMX(FILTER('Sales', 
            YEAR('Sales'[Date]) = PrevMonthYear &&
            MONTH('Sales'[Date]) = PrevMonth
            ),
    'Sales'[ActiveSales]),
    ALL('Sales'[Date])
)

var PreviousQuarterActiveSales := 
CALCULATE(

var PrevQuarterYear = IF(QUARTER(TODAY()) == 1, YEAR(TODAY())-1, YEAR(TODAY()))
var PrevQuarter = IF(QUARTER(TODAY()) == 1, 4, QUARTER(TODAY()) - 1)

SUMX(FILTER('Sales', 
            YEAR('Sales'[Date]) = PrevQuarterYear &&
            QUARTER('Sales'[Date]) = PrevQuarter
            ),
    'Sales'[ActiveSales]),
    ALL('Sales'[Date])
)


var PreviousYearActiveSales := 
CALCULATE(

var PrevYear = YEAR(TODAY()) - 1

SUMX(FILTER('Sales', 
            YEAR('Sales'[Date]) = PrevYear
            ),
    'Sales'[ActiveSales]),
    ALL('Sales'[Date])
)


FirstTablePrevPeriodSales = 
SWITCH
(
    SELECTEDVALUE(DebitSalesDateSlicer[Period]),
    "MTD", PreviousMonthActiveSales,
    "QTD", PreviousQuarterActiveSales,
    "YTD", PreviousYearActiveSales
)

 

 

  • darkmetrics Maybe:

    My Measure = 
    
    var PrevMonthYear = IF(MONTH(TODAY()) == 1, YEAR(TODAY())-1, YEAR(TODAY()))
    var PrevMonth = IF(MONTH(TODAY()) == 1, 12, MONTH(TODAY()) - 1)
    var PreviousMonthActiveSales :=
    CALCULATE(
    SUMX(FILTER('Sales', 
                YEAR('Sales'[Date]) = PrevMonthYear &&
                MONTH('Sales'[Date]) = PrevMonth
                ),
        'Sales'[ActiveSales]),
        ALL('Sales'[Date])
    )
    
    var PrevQuarterYear = IF(QUARTER(TODAY()) == 1, YEAR(TODAY())-1, YEAR(TODAY()))
    var PrevQuarter = IF(QUARTER(TODAY()) == 1, 4, QUARTER(TODAY()) - 1)
    
    var PreviousQuarterActiveSales := 
    CALCULATE(
    SUMX(FILTER('Sales', 
                YEAR('Sales'[Date]) = PrevQuarterYear &&
                QUARTER('Sales'[Date]) = PrevQuarter
                ),
        'Sales'[ActiveSales]),
        ALL('Sales'[Date])
    )
    
    var PrevYear = YEAR(TODAY()) - 1
    
    var PreviousYearActiveSales := 
    CALCULATE(
    SUMX(FILTER('Sales', 
                YEAR('Sales'[Date]) = PrevYear
                ),
        'Sales'[ActiveSales]),
        ALL('Sales'[Date])
    )
    
    FirstTablePrevPeriodSales = 
    SWITCH
    (
        SELECTEDVALUE(DebitSalesDateSlicer[Period]),
        "MTD", PreviousMonthActiveSales,
        "QTD", PreviousQuarterActiveSales,
        "YTD", PreviousYearActiveSales
    )
    
    RETURN
    FirstTablePrevPeriodSales 

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion

    darkmetrics Maybe:

    My Measure = 
    
    var PrevMonthYear = IF(MONTH(TODAY()) == 1, YEAR(TODAY())-1, YEAR(TODAY()))
    var PrevMonth = IF(MONTH(TODAY()) == 1, 12, MONTH(TODAY()) - 1)
    var PreviousMonthActiveSales :=
    CALCULATE(
    SUMX(FILTER('Sales', 
                YEAR('Sales'[Date]) = PrevMonthYear &&
                MONTH('Sales'[Date]) = PrevMonth
                ),
        'Sales'[ActiveSales]),
        ALL('Sales'[Date])
    )
    
    var PrevQuarterYear = IF(QUARTER(TODAY()) == 1, YEAR(TODAY())-1, YEAR(TODAY()))
    var PrevQuarter = IF(QUARTER(TODAY()) == 1, 4, QUARTER(TODAY()) - 1)
    
    var PreviousQuarterActiveSales := 
    CALCULATE(
    SUMX(FILTER('Sales', 
                YEAR('Sales'[Date]) = PrevQuarterYear &&
                QUARTER('Sales'[Date]) = PrevQuarter
                ),
        'Sales'[ActiveSales]),
        ALL('Sales'[Date])
    )
    
    var PrevYear = YEAR(TODAY()) - 1
    
    var PreviousYearActiveSales := 
    CALCULATE(
    SUMX(FILTER('Sales', 
                YEAR('Sales'[Date]) = PrevYear
                ),
        'Sales'[ActiveSales]),
        ALL('Sales'[Date])
    )
    
    FirstTablePrevPeriodSales = 
    SWITCH
    (
        SELECTEDVALUE(DebitSalesDateSlicer[Period]),
        "MTD", PreviousMonthActiveSales,
        "QTD", PreviousQuarterActiveSales,
        "YTD", PreviousYearActiveSales
    )
    
    RETURN
    FirstTablePrevPeriodSales