Create measure with relative date filtering?

Question

Hi!&nbsp;I have what I hope is a simple question regarding creating a measure.&nbsp;I have (1) table that contains Sales by&nbsp;Date*&nbsp;by Order Type (Invoiced, Open, Shipped).&nbsp;Order TypeInvoiced DateOpen DateShipped DateSalesInvoiced9/1/2018&nbsp;&nbsp;1000Invoiced10/1/2018&nbsp;&nbsp;1000Invoiced10/2/2018&nbsp;&nbsp;1000Shipped&nbsp;&nbsp;10/1/20182000Shipped&nbsp;&nbsp;10/10/20182000Open&nbsp;10/15/2018&nbsp;3000Open&nbsp;10/20/2018&nbsp;3000Open&nbsp;10/25/2018&nbsp;3000Open&nbsp;11/1/2018&nbsp;3000Open&nbsp;12/1/2018&nbsp;3000I have (3) card visuals in my application, which are restricted on relative date filtering for the approriate date field&nbsp;Invoiced Total = 2000 (relative date filtering Invoiced Date = "in this month")Open Total = 9000 (relative date filtering on Open Date = "in this month")Shipped Total = 4000 (relative date filtering on Shipped Date = "in this month")&nbsp;I would like to have a 4th card visual called "Projected Total", this would be the sum of the Invoiced + Open + Shipped, but with the relative date filtering for the current month. So, the Projected Total would = 16000&nbsp;&nbsp;Is there an easy way to accomplish this?&nbsp;

brysonds · Accepted Answer

Hi Everyone,&nbsp;Quick update - I had an idea on how to solve this (not sure if it's efficient or not, so still open to suggestions).&nbsp;I created a calculated column for each of the order types:&nbsp;CurrentMonth_Invoiced =&nbsp; &nbsp; IF (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;YEAR ('Table1'[Invoice Date]) = YEAR (TODAY () )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; Month ('Table1'[Invoice Date]) = MONTH (TODAY () ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Table1'[Sales Amount (USD)],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp;....repeat for the other two using the other dates...&nbsp;Then I created a measure to sum eacch of the columns together:&nbsp;CurrentMonth_Total = SUM('Table1'[CurrentMonth_Invoiced])+SUM('Table1'[CurrentMonth_Open])+SUM('Table1'[CurrentMonth_SNI])&nbsp;Like I said, I am not sure if this is the best approach or not, but it does work.&nbsp;Thanks ahead of time if anyone can provide a better solution.

Forum Discussion

Create measure with relative date filtering?

1 Reply

Related content

Time Relative filter

Dynamic measure filtering

Filter by Rank measure

Relative date filters resetting

Relative Date Filter suddenly stopped working

Order Type	Invoiced Date	Open Date	Shipped Date	Sales
Invoiced	9/1/2018			1000
Invoiced	10/1/2018			1000
Invoiced	10/2/2018			1000
Shipped			10/1/2018	2000
Shipped			10/10/2018	2000
Open		10/15/2018		3000
Open		10/20/2018		3000
Open		10/25/2018		3000
Open		11/1/2018		3000
Open		12/1/2018		3000