Create measure with relative date filtering?

[Brysonds]

Hi!

I have what I hope is a simple question regarding creating a measure.

I have (1) table that contains Sales by Date* by Order Type (Invoiced, Open, Shipped).

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

I have (3) card visuals in my application, which are restricted on relative date filtering for the approriate date field

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

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 

Is there an easy way to accomplish this?

[Brysonds · Accepted solution]

Hi Everyone,

Quick update - I had an idea on how to solve this (not sure if it's efficient or not, so still open to suggestions).

I created a calculated column for each of the order types:

CurrentMonth_Invoiced =
    IF (
         YEAR ('Table1'[Invoice Date]) = YEAR (TODAY () )
                  && Month ('Table1'[Invoice Date]) = MONTH (TODAY () ),
                  'Table1'[Sales Amount (USD)],
                   0
        )

....repeat for the other two using the other dates...

Then I created a measure to sum eacch of the columns together:

CurrentMonth_Total = SUM('Table1'[CurrentMonth_Invoiced])+SUM('Table1'[CurrentMonth_Open])+SUM('Table1'[CurrentMonth_SNI])

Like I said, I am not sure if this is the best approach or not, but it does work.

Thanks ahead of time if anyone can provide a better solution.