Add Date Variable Filter to Calculate

[ptmuldoon]

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>

How do you swap the second filter of DATESINPERIOD withe MyPeriod variable?

Var Myitem = "OCCUPIED ROOMS"

    Var MyPeriod = 
        SWITCH (
		    VALUES ('Measures List'[Fact Period]),
	    	    "Last 7", DATESINPERIOD ( 'Dim Period'[Date], MAX ( 'Dim Period'[Date] ), -7, DAY ),
		        "MTD", DATESMTD( 'Dim Period'[Date] ),
		        "QTD", DATESQTD( 'Dim Period'[Date] ),
		        "YTD", DATESYTD( 'Dim Period'[Date] )
	    )

    RETURN

        SWITCH (
            VALUES ('FactType'[FactType]),
                "#", CALCULATE(
                    [Qty]
                    , 'Dim Item'[description]=Myitem 
                    //, MyPeriod  // does not work
                    //, 'Dim Period'=MyPeriod  // does not work
                    //, Filter('Dim Period','Dim Period'=MyPeriod)  // does not work
                    ,  DATESINPERIOD ( 'Dim Period'[Date], MAX ( 'Dim Period'[Date] ), -7, DAY) // this works
                ),

.........More code

[ptmuldoon · Accepted solution]

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
                ),