cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper III

Create measure with relative date filtering?

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?

1 ACCEPTED SOLUTION
Helper III

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.

Helper III

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.

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors