Forum Discussion

JNelson's avatar
JNelson
Helper II
2 years ago
Solved

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

 

 

datesadd
datesbetween
DAX
  • ChiragGarg2512's avatar
    ChiragGarg2512
    2 years ago

    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

  • ChiragGarg2512's avatar
    ChiragGarg2512
    Solution Sage
    2 years ago

    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.