Forum Discussion

ptmuldoon's avatar
ptmuldoon
Resolver I
1 year ago
Solved

Month/Day Variable within DATESINPERIOD?

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 wa...
Need Help
  • OwenAuger's avatar
    OwenAuger
    1 year ago

    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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.