Dynamic date function

[Anonymous]

My following measure works, however, I want to include a dynamic date range, so I don't have to update the measures every QT. What would be the best Dax to input to make this dynamic? 

Test Pipeline = CALCULATE(SUM(OpportunityFORREPORTING[opportunity.Net_New_Sales__c]), OpportunityFORREPORTING[opportunity.CreatedDate] <DATE(2019,10,01), AND(OpportunityFORREPORTING[opportunity.CloseDate] >=DATE(2019,10,01), OpportunityFORREPORTING[opportunity.CloseDate] <=DATE(2020,01,01)))

I need this to be dynamic and right now it's static.

[v-joesh-msft · Accepted solution]

Hi @Anonymous ,

I think you might want to get the first day of the current quarter and the last day of the current quarter, you can try the following measures:

FirstQuaterDay =
VAR TodaysDate =
    TODAY ()
VAR Calendar1 =
    CALENDAR (
        DATE ( YEAR ( TodaysDate ), 1, 1 ),
        DATE ( YEAR ( TodaysDate ), 12, 31 )
    )
VAR Calendar2 =
    ADDCOLUMNS ( Calendar1, "Quarter", ROUNDUP ( MONTH ( [Date] ) / 3, 0 ) )
VAR TodaysMonth =
    ROUNDUP ( MONTH ( TodaysDate ) / 3, 0 )
VAR Calendar3 =
    FILTER ( Calendar2, [Quarter] = TodaysMonth )
VAR FirstQUaterDay =
    MINX ( Calendar3, [Date] )
RETURN
    FirstQUaterDay

LastQuaterDay =
VAR TodaysDate =
    TODAY ()
VAR Calendar1 =
    CALENDAR (
        DATE ( YEAR ( TodaysDate ), 1, 1 ),
        DATE ( YEAR ( TodaysDate ), 12, 31 )
    )
VAR Calendar2 =
    ADDCOLUMNS ( Calendar1, "Quarter", ROUNDUP ( MONTH ( [Date] ) / 3, 0 ) )
VAR TodaysMonth =
    ROUNDUP ( MONTH ( TodaysDate ) / 3, 0 )
VAR Calendar3 =
    FILTER ( Calendar2, [Quarter] = TodaysMonth )
VAR LastQuaterDay =
    MAXX ( Calendar3, [Date] )+1
RETURN
    LastQuaterDay

Results are as follows:

Adjust the measure as bellow:

Test Pipeline =
CALCULATE (
    SUM ( OpportunityFORREPORTING[opportunity.Net_New_Sales__c] ),
    OpportunityFORREPORTING[opportunity.CreatedDate] < [FirstQuaterDay],
    AND (
        OpportunityFORREPORTING[opportunity.CloseDate] >= [FirstQuaterDay],
        OpportunityFORREPORTING[opportunity.CloseDate] <= [LastQuaterDay]
    )
)

For more details, you can refer to this post: First/Last Working Days

 Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EYbREQKmpXFAlhrtD2C03sYBpaurBk6ObA9xmig93lY2Ig?e=mdj6N2

Best Regards,

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

[Anonymous]

Can this be used to filter the values on the report? I have a dynamic report but I want to filter the data for previous month.

[Anonymous]

I wrapped individually in filters and it worked. Thank you so much!

Test Pipeline =

CALCULATE(SUM(OpportunityFORREPORTING[opportunity.Net_New_Sales__c]),FILTER(ALL(OpportunityFORREPORTING), OpportunityFORREPORTING[opportunity.CreatedDate].[Date] <[FirstQuaterDay]), FILTER(ALL(OpportunityFORREPORTING),(OpportunityFORREPORTING[opportunity.CloseDate] >= [FirstQuaterDay])), FILTER(ALL(OpportunityFORREPORTING), OpportunityFORREPORTING[opportunity.CloseDate] <=[LastQuaterDay]))

[Anonymous]

Thank you for your assistance. The last measure has an error message of "A function 'Calculate' that has been used un a True/False expression that is used as a table filter expression. This is not allowed."

[jthomson]

DAX has a start of quarter function, you could try working out what that spits out in conjunction with TODAY() (adding three months as needed)?