topic Re: Calculating quote ages in DAX Commands and Tips

Calculating quote ages

JNelson — Tue, 03 Oct 2023 04:55:06 GMT

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

Re: Calculating quote ages

ChiragGarg2512 — Tue, 03 Oct 2023 12:25:04 GMT

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.

Re: Calculating quote ages

JNelson — Tue, 03 Oct 2023 22:20:12 GMT

Perfect! Thank you so much