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. ...
  • Greg_Deckler's avatar
    3 years ago

    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