Month/Day Variable within DATESINPERIOD?

[ptmuldoon]

I'm trying to find a way if it possibly to use a variable or alternative when using DATESINPERIOD.  The function tells me it must have a key word of Day, Month, Quarter or Year.

So, my thought was trying to create a variable using Switch to determine between Day and Month, but when I try that I get true/false expression errror.

In the below, I was trying to replace the VAR periodRange with testRange, but could not get it work.   Is something like this possible?

 VAR periodRange = DATESINPERIOD('Date Table'[Date], maxDate, -Number, MONTH)

    VAR testRange = 
        Switch(SELECTEDVALUE(APICalls[APICall]),
                "Daily", DATESINPERIOD('Date Table'[Date], maxDate, -Number, DAY),
                "Monthly", DATESINPERIOD('Date Table'[Date], maxDate, -Number, MONTH)
        )
    
 RETURN
    CALCULATE(
        [Amt],
        REMOVEFILTERS('Dim Period'),
        KEEPFILTERS(testRange),
        USERELATIONSHIP('Dim Period'[Date], 'Date Table'[Date])
    )

[Anonymous]

Hi @ptmuldoon, hello OwenAuger, thank you for your prompt reply! 

The "KEEPFILTERS(testRange)" will return a table of time periods, not the True/False expression.

Depending on your requirements, we could also use EDATE function to return the date that is the indicated number of months before or after the start date.

The sample test is for your reference and you can modify it according to your own situation:

Measure = 
VAR rangeStart = 
    IF(
        SELECTEDVALUE('Table'[Type])="Daily", 
       'Date'[RangeEnd]-1, 
       EDATE('Date'[RangeEnd],-1)
    )

VAR rangeEnd = 
    MAX('Date'[Date])

RETURN
    CALCULATE(
        SUM('Date'[Aim]),
        FILTER(
            'Date',
            'Date'[Date] >= rangeStart && 'Date'[Date] <= rangeEnd   
        )
    )

Result:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[OwenAuger · Accepted solution]

Hi @ptmuldoon 

1. The 4th argument of DATESINPERIOD must be one of the keywords (DAY, MONTH, QUARTER, YEAR) but cannot be any other expression. In fact, there is no way to return one of these four keywords with any other expression.

2. The functions IF or SWITCH cannot be used to return tables. They can only return scalar values.
However, a different approach to produce a "conditional" table is to FILTER each possible table with a boolean expression that is true/false per table, and take the union.

For example, you could write:

VAR APICallValue =
    SELECTEDVALUE ( APICalls[APICall] )
VAR FilterDay =
    FILTER (
        DATESINPERIOD ( 'Date Table'[Date], maxDate, - Number, DAY ),
        APICallValue = "Daily"
    )
VAR FilterMonth =
    FILTER (
        DATESINPERIOD ( 'Date Table'[Date], maxDate, - Number, MONTH ),
        APICallValue = "Monthly"
    )
VAR FilterSelected =
    UNION ( FilterDay, FilterMonth ) // only one of the two tables is non-empty
RETURN
    CALCULATE (
        [Amt],
        REMOVEFILTERS ( 'Dim Period' ),
        KEEPFILTERS ( FilterSelected ),
        USERELATIONSHIP ( 'Dim Period'[Date], 'Date Table'[Date] )
    )

Alternatively, if you want to use SWITCH, you would need to restate the CALCULATE expressions, with the only difference being the 4th argument of DATESINPERIOD.

SWITCH (
    SELECTEDVALUE ( APICalls[APICall] ),
    "Daily",
        CALCULATE (
            [Amt],
            REMOVEFILTERS ( 'Dim Period' ),
            KEEPFILTERS ( DATESINPERIOD ( 'Date Table'[Date], maxDate, - Number, DAY ) ),
            USERELATIONSHIP ( 'Dim Period'[Date], 'Date Table'[Date] )
        ),
    "Monthly",
        CALCULATE (
            [Amt],
            REMOVEFILTERS ( 'Dim Period' ),
            KEEPFILTERS ( DATESINPERIOD ( 'Date Table'[Date], maxDate, - Number, MONTH ) ),
            USERELATIONSHIP ( 'Dim Period'[Date], 'Date Table'[Date] )
        )
)

(You could create separate Daily/Monthly measures and reference them here but the structure remains the same.)

Do any of those ideas help?

Regards

[ptmuldoon]

Thank you for your help!!.  And after some thought last night, I had worked out and came up with the same thing in your second solution as the work around.  But I may try that first solution as well to try and keep the Calcuate function a little cleaner.