DAX relative date filtering

[mikemi]

I am looking to create two relative date measures for the same field "Gross": Last 30 Days, and Quarter-To-Date.

I created a quick measure but unsure how to filter for the two relative dates above. The end goal is to create a table that shows "Gross" created in the last 30 days, and quarter-to-date (which I don't believe can be done without two separate measures if I want to include them in the same table).

Below is the quick measure where I was only able to select one date. I assume the IN { DATE(2018, 4, 27) section is the chunk that needs to be replaced.

Gross for Friday, April 27, 2018 = 
CALCULATE(
	SUM('Opportunity'[Gross]),
	'Opportunity'[Created Date] IN { DATE(2018, 4, 27) }
)

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[mikemi]

Thanks, @Ashish_Mathur.

I have the file ready to upload but not sure how to share as a link. Suggestions?

[Ashish_Mathur]

Hi,

Upload the file to OneDrive/Google Drive and share the download link here.

[mikemi]

Thanks @Ashish_Mathur, here is the file: https://drive.google.com/file/d/1fZIutEv-LwBPn6UDkoYbD04sI9wlQ7LM/view?usp=sharing

[Ashish_Mathur]

Hi,

Try this measure

=CALCULATE(SUM('Opportunity'[Gross]),DATESBETWEEN(DateKey[Date],MIN(DateKey[Date])-30,MIN(DateKey[Date])))

Hope this helps.

[yipioz]

Great measure ! Works for me as well 🙂 Thanks

[mikemi]

Thanks! That works for last 30 days. How would quarter-to-date work?

[Ashish_Mathur]

Hi,

Try this measure for QTD

=CALCULATE(SUM('Opportunity'[Gross]),DATESBETWEEN(DateKey[Date],EDATE(MIN(DateKey[Date]),-3),MIN(DateKey[Date])))

[mikemi]

Now neither are working - both measures are the DAX you mentioned. Thoughts?

[Ashish_Mathur]

Hi,

Create a slicer and drag the Date column from the DateKey table.  Select any specific date there.