Forum Discussion
ptmuldoon
1 year agoResolver I
Add Date Variable Filter to Calculate
I'm trying to add a Variable Date filter into a calculate function, and struggling to get this work. I think/beleive it should be doable, but struggling if need to explicite include FILTER or not> ...
- 1 year ago
I figured this out, and again it makes perfect sense once I wrap my head aroudn the logic of it. The variables will (I think) always compute their values first before you get into the Calculate function. So the 'trick' was to compute the number of days in the Switch Variable, and then pass that number into the Return/Calculate.
So this works and computes as expected.
Var MyPeriod = SWITCH ( VALUES ('Measures List'[Fact Period]), "Day", 1, "Last 7", 7, "MTD", DATEDIFF( STARTOFMONTH('Dim Period'[Date]), MAX('Dim Period'[Date]), DAY ) + 1, "QTD", DATEDIFF( STARTOFQUARTER('Dim Period'[Date]), MAX('Dim Period'[Date]), DAY ) + 1, "YTD", DATEDIFF( STARTOFYEAR('Dim Period'[Date]), MAX('Dim Period'[Date]), DAY ) + 1 ) RETURN SWITCH ( VALUES ('FactType'[FactType]), "#", CALCULATE( [Qty] , 'Dim Item','Dim Item'[description]=Myitem , DATESINPERIOD ( 'Dim Period'[Date], MAX ( 'Dim Period'[Date] ), -VALUE(MyPeriod), DAY) // this works ),
ptmuldoon
1 year agoResolver I
I figured this out, and again it makes perfect sense once I wrap my head aroudn the logic of it. The variables will (I think) always compute their values first before you get into the Calculate function. So the 'trick' was to compute the number of days in the Switch Variable, and then pass that number into the Return/Calculate.
So this works and computes as expected.
Var MyPeriod =
SWITCH (
VALUES ('Measures List'[Fact Period]),
"Day", 1,
"Last 7", 7,
"MTD", DATEDIFF( STARTOFMONTH('Dim Period'[Date]), MAX('Dim Period'[Date]), DAY ) + 1,
"QTD", DATEDIFF( STARTOFQUARTER('Dim Period'[Date]), MAX('Dim Period'[Date]), DAY ) + 1,
"YTD", DATEDIFF( STARTOFYEAR('Dim Period'[Date]), MAX('Dim Period'[Date]), DAY ) + 1
)
RETURN
SWITCH (
VALUES ('FactType'[FactType]),
"#", CALCULATE(
[Qty]
, 'Dim Item','Dim Item'[description]=Myitem
, DATESINPERIOD ( 'Dim Period'[Date], MAX ( 'Dim Period'[Date] ), -VALUE(MyPeriod), DAY) // this works
),