Dynamic Dates

[dpotta]

Hi

In the following measure I would like to replace the DATE(2016,9,30) with a Dynamic Date based on the End Date of The Previous Quarter by using TODAY().

I've tried a few things but I'm not sure where I am going wrong. Any suggestions most welcome.

CreatedPriorToEndOfLastQuarter = calculate ([PI_All Rows], filter(vwProperty,vwProperty[Date Created]<=DATE(2016,9,30)))

Thank you

Dave

 

[akice]

If you want the last date of the previous quarter using a separate, standard practice Calendar table, this is easy enough:

[COLOR=#333333]CreatedPriorToEndOfLastQuarter = calculate ([PI_All Rows], filter(vwProperty,vwProperty[Date Created]<=ENDOFQUARTER(PREVIOUSQUARTER(Calendar[Date]))  ))[/COLOR]

But this has a number of caveats: First, It determines the current quarter based on first date visible in the current filter context. Second, it works using time intelligence functions which really requires the separate Calendar table (In fact any way you go, you really need this separate table). Time Intelligence functions works by shifting the dates visible in the current filter context for the Calendar table but won't "make" dates that don't already exist in the Calendar table.

If you really need to use TODAY() function (meaning you want previous quarter based on system date of when report is run ) then while i have not tried it, something like this should work. Replace my change above with this:

FILTER ( ALL(Calendar), Calendar[Date] = TODAY() )

If you are using Power BI Desktop or Excel 2016, I'd put the code result into a variable first.

VAR Todays_Date = FILTER ( ALL(Calendar), Calendar[Date] = TODAY() )

[COLOR=#333333]CreatedPriorToEndOfLastQuarter = calculate ([PI_All Rows], filter(vwProperty,vwProperty[Date Created]<=Todays_Date  ))[/COLOR]