cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Dynamic date function

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.
1 ACCEPTED SOLUTION

Accepted Solutions
New Contributor

## Re: Dynamic date function

Hi @dwatson ,

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:

```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/EYbREQKmpXFAlhrtD2...

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.

4 REPLIES 4
New Contributor

## Re: Dynamic date function

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

New Contributor

## Re: Dynamic date function

Hi @dwatson ,

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:

```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/EYbREQKmpXFAlhrtD2...

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.

Frequent Visitor

## Re: Dynamic date function

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."

Frequent Visitor

## Re: Dynamic date function

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

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!