DAX measure to output Quote Aging label based on Quote Issued date and current date

[ZAIWILL]

Hi, I am learning PowerBI and been able to write a few Measures to get my the Below report which is for Customer Quotes that have been issued so far in 2024, for Country Code "PG".

- Have written a measure to return the Age of each record called "AGE Measure" which is shown in the "Age" Column and been trying to use that AGE Measure to write another measure that will give me the Aging lables for each record.

-  The measure I am have is as follows = 

Quote Age  = IF(SELECTEDVALUE(TableName[AGE Measure])<=8, "01 - 07 days old",

                IF(SELECTEDVALUE(TableName[AGE Measure])<=14,"08 - 14 days old",

                IF(SELECTEDVALUE(PNLTracker[AGE Measure])<=21,"15 - 21 days old",

                IF(SELECTEDVALUE(PNLTracker[AGE Measure])<=31,"22 - 31 days old",

                "Month+ OVERDUE"))))

I dont think it works correctly for what I want to achieve.

2. Requirement 2, I want to created CARDS that will count each of the Grouped Ageing Lables and display the totals on each of the cards.

Pls refer to the below screenshot. Hope someone can help me out. Thanks.

[shafiz_p · Accepted solution]

Hi @ZAIWILL 

To achieve the 2 part you need to create 5 measure. Try this below code:

01-07 days = 
CALCULATE(
    COUNTROWS(TableName),
    FILTER(
        TableName,
        [AGE Measure] <= 7
    )
)

08-14 days = 
CALCULATE(
    COUNTROWS(TableName),
    FILTER(
        TableName,
        [AGE Measure] > 7 && [AGE Measure] <= 14
    )
)

15-21 days = 
CALCULATE(
    COUNTROWS(TableName),
    FILTER(
        TableName,
        [AGE Measure] > 14 && [AGE Measure] <= 21
    )
)

22-31 days = 
CALCULATE(
    COUNTROWS(TableName),
    FILTER(
        TableName,
        [AGE Measure] > 21 && [AGE Measure] <= 31
    )
)

Month+ Overdue = 
CALCULATE(
    COUNTROWS(TableName),
    FILTER(
        TableName,
        [AGE Measure] > 31
    )
)

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[ZAIWILL · Accepted solution]

Still no luck with the CARDs are still not calculating correctly as per screenshot - Thanks

[ZAIWILL]

Thank you all for your input and the Solutions you have provided are workable. In the end I found what the cause was and it was to do with the FILTER declaration I set in the Filter so return only "Quote Issued" records. Once I removed that Filter, the count records started to match. See screenshot.

[v-nuoc-msft · Accepted solution]

Hi @ZAIWILL 

Thank you very much johnt75 and shafiz_p for your prompt reply.

For your question, here is the method I provided:

Here's some dummy data

"PNLTracker"

Create measures.

AGE Measure = DATEDIFF(SELECTEDVALUE('PNLTracker'[ModifiedOn]), TODAY(), DAY)

Quote Age = 
IF(
    ISINSCOPE(PNLTracker[K2 Reference]),
    SWITCH(
        TRUE(),
        [AGE Measure] <= 7, "01 - 07 days old",
        [AGE Measure] <= 14, "08 - 14 days old",
        [AGE Measure] <= 21, "15 - 21 days old",
        [AGE Measure] <= 31, "22 - 31 days old",
        "Month+ OVERDUE"
    ),
    BLANK()
)

Count 01_07 = COUNTROWS(FILTER(PNLTracker, [Quote Age] = "01 - 07 days old"))

Count 08_14 = COUNTROWS(FILTER(PNLTracker, [Quote Age] = "08 - 14 days old"))

Count 15_21 = COUNTROWS(FILTER(PNLTracker, [Quote Age] = "15 - 21 days old"))

Count 22_31 = COUNTROWS(FILTER(PNLTracker, [Quote Age] = "22 - 31 days old"))

Count Month_Overdue = COUNTROWS(FILTER(PNLTracker, [Quote Age] = "Month+ OVERDUE"))

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shafiz_p · Accepted solution]

Hi @ZAIWILL 

To achieve the 2 part you need to create 5 measure. Try this below code:

01-07 days = 
CALCULATE(
    COUNTROWS(TableName),
    FILTER(
        TableName,
        [AGE Measure] <= 7
    )
)

08-14 days = 
CALCULATE(
    COUNTROWS(TableName),
    FILTER(
        TableName,
        [AGE Measure] > 7 && [AGE Measure] <= 14
    )
)

15-21 days = 
CALCULATE(
    COUNTROWS(TableName),
    FILTER(
        TableName,
        [AGE Measure] > 14 && [AGE Measure] <= 21
    )
)

22-31 days = 
CALCULATE(
    COUNTROWS(TableName),
    FILTER(
        TableName,
        [AGE Measure] > 21 && [AGE Measure] <= 31
    )
)

Month+ Overdue = 
CALCULATE(
    COUNTROWS(TableName),
    FILTER(
        TableName,
        [AGE Measure] > 31
    )
)

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[ZAIWILL]

Thanks @shafiz_p, I think you measure works but I have a feeling there is something not correct which I cannot put a finger on.  See the results after I created the 5 Measures and added them to the Cards. The CARDS counts are not always matching with the Table records. Any other help is appreciated.

[shafiz_p]

try using ALLSELECTED in all measure. See image below:

Make sure all relationships and slicers and other filters are correctly set to reflect.

hope this helps!!

[ZAIWILL · Accepted solution]

Still no luck with the CARDs are still not calculating correctly as per screenshot - Thanks

[johnt75]

Try

Quote Age =
IF (
    ISINSCOPE ( TableName[K2 Reference] ),
    SWITCH (
        TRUE (),
        [AGE Measure] <= 8, "01 - 07 days old",
        [AGE Measure] <= 14, "08 - 14 days old",
        [AGE Measure] <= 21, "15 - 21 days old",
        PNLTracker[AGE Measure] <= 31, "22 - 31 days old",
        "Month+ OVERDUE"
    )
)

The ISINSCOPE is to make sure that no value is returned for the Total row.

[ZAIWILL]

thanks for the reply, I tried your measure codes and when inseting my AGE measure, it was not "found". Cannot figure out why. Have added my Age Measure here FYR.