Forum Discussion
darkmetrics
Helper I
3 years agoHow 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. ...
- 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
Greg_Deckler
Community Champion
3 years agodarkmetrics 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 - darkmetrics3 years ago
Helper I
Thank you, now it works.