Measure to calculate number of records within a period of time.

[rfwjr64]

Hello, I am relatively new to writing PBI measures.  I am trying to calculate the following:

I want to provide a status of the last 30 days, 60 days, etc. from the approval date (which is a column) based on the status of another column (the column being Quote Status).  If the quote status is "No Quote Received", How many records that were received in the last 30 days has that status.  (then I want to provide the same status for the last 60 days).  

If somebody can guide me I would appreciate it.  I would also need to know how to apply a solution (e.g. click on New Measure, etc).

Thanks very much in advance. 

[amitchandak]

@rfwjr64 , see a measure like this can help

Example

Rolling 30 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-30,Day))

[rfwjr64]

Thank you for replying amitchandak .  Can you also include the condition that another column ('Quote status') needs to be set to 'No Quote Received' in order to be calculated.  If the status is something else, no calculation is required. 

[Anonymous · Accepted solution]

Hi @rfwjr64,

You can also use the date function to manually defined the rolling calculation ranges. It should more agility than time intelligence functions.

rolling one month=
VAR currDate =
    MAX ( 'Table'[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Date] >= DATE ( YEAR ( currDate ), MONTH ( currDate )-1, DAY ( currDate ) )
                && [Date] <= currDate
        )
    )

In addition, if you want to reference other column value as the condition in the formula, please make sure they has relationships then you can simply add them to current expressions.

rolling one month=
VAR currDate =
    MAX ( 'Table'[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Date]
                >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) )
                && [Date] <= currDate
        ),
        FILTER ( ALLSELECTED ( Table2 ), [Column] = "xxxxx" )
    )

Regards,

Xiaoxin Sheng