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 II

## Calculating quote ages

Hello,

I have a data set with month and count of quotes. I need a measure that will calculate Count the number of quotes that are: current (1 - 4 months); 5 - 7 months old; 8 - 12months old and greater than 12 months old based off today's current month.

An example of what I want is below. I will then put the measures into a bar chart to see the "age" of the quotes.

TIA

1 ACCEPTED SOLUTION
Super User

@JNelson , First create a month bucket[column] where there can be a classification of month partition.

Ex. Column

Month Bucket =
var _diff = datediff([Date], today(), month) +1
return
Switch(true(),
_diff <=4 , "   1 - 4 Months",
_diff <=7 , "  5 - 7 Months",
_diff <=12 , " 8 - 12 Months",
">12 Months"
)

After creating the above column create a measure
sum = Calculate(SUMX(q4, q4[No. of Quotes]), FILTER(ALL(q4), q4[Month Bucket] = SELECTEDVALUE(q4[Month Bucket])))

Replace q4 with the table name.

2 REPLIES 2
Super User

@JNelson , First create a month bucket[column] where there can be a classification of month partition.

Ex. Column

Month Bucket =
var _diff = datediff([Date], today(), month) +1
return
Switch(true(),
_diff <=4 , "   1 - 4 Months",
_diff <=7 , "  5 - 7 Months",
_diff <=12 , " 8 - 12 Months",
">12 Months"
)

After creating the above column create a measure
sum = Calculate(SUMX(q4, q4[No. of Quotes]), FILTER(ALL(q4), q4[Month Bucket] = SELECTEDVALUE(q4[Month Bucket])))

Replace q4 with the table name.

Helper II

Perfect! Thank you so much

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