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.
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
Community 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- darkmetrics
Helper I
Thank you, now it works.