Aging segmentation help

DAX DAX Calculations
1

Hi,

Need some help on my Aging calculation. I following the tricks from Sam here on create a table for the grouping and then create measure from the matrix placement.

But I don;t know why it doesn’t put at the correct group as it is always falls into 1st group.

The step is like this :

  1. In my transaction, based on field Transaction date I created this measure →

    Aging = CALCULATE(

                DATEDIFF(
                    SELECTEDVALUE(Trans[TRANSDATE]),
                                NOW(),
                                DAY
                        ),
                        ALLSELECTED('Dates'[DATE_])

)
One reason I’m using ALLSELECTED in the end is actually I made the date filter to be adjustable, but this is another story, and not an issue.

  1. Because originally I’m using Direct Query, I have to use TempTable inside my “placement” measure like below → 
Age QTY = 
VAR TmpTable = ADDCOLUMNS(
                            VALUES(Trans[ITEMID]),
                            "TmpAging",
                            CALCULATE(
                                        [Aging],
                                        CALCULATETABLE(
                                            VALUES(Trans[ITEMID])
                                        ),
                                        ALLSELECTED()
                            )
)
RETURN CALCULATE(
                SUM(Trans[BALQTY]),
                FILTER(
                        TmpTable,
                        COUNTROWS(
                                    FILTER('Interval',
                                    [TmpAging] >= 'Interval'[FROM] &&
                                    [TmpAging] <= 'Interval'[TO]
                                    )
                        ) > 0
                )                
)

I’ve put my pbix for reference and changed the mode to import mode in drop box in this link →

Hope the file can be downloaded and use.

Much thanks in advance,
Toni

2

Hi @Toni, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
    image
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

3

@Toni
I modified the following codes and it is working now:

Aging = 
CALCULATE (
    DATEDIFF ( SELECTEDVALUE ( Trans[TRANSDATE] ), NOW (), DAY ),
    VALUES( ( Trans[TRANSDATE] )
)
                        )


Age QTY = 
VAR TmpTable =
    ADDCOLUMNS (
        SUMMARIZE ( Trans, Trans[ITEMID], Trans[TRANSDATE] ),
        "@TmpAging", [Aging]
    )
RETURN
    CALCULATE (
        SUM ( Trans[BALQTY] ),
        FILTER (
            TmpTable,
            COUNTROWS (
                FILTER (
                    'Interval',
                    [@TmpAging] >= 'Interval'[FROM]
                        && [@TmpAging] <= 'Interval'[TO]
                )
            ) > 0
        )
    )

1
11431×637 38.6 KB

Attaching the files for your reference.

ErrorAging.pbix (293.6 KB)

Thanks.

2 Likes
4

Hi MudassirAli,

It works. Thank you very much. And it is much simpler as well.
Mind to tell me what is the logic behind it, so I can understand. Also of course why my previous is not working, because if I follow over here is like so, and actually once used it but for other report and it used to works. Understand of course it maybe because data differs, but not as I remembered. Sorry, to trouble you.

Again, thanks.

5

Hi @Toni, did the response provided by @ MudassirAli help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

1 Like
6

Hi,

Yes, it is solved. Only I actually want to know the problem why it is not working with my previous measure.
But it’s ok.

Thanks