Forum Discussion

eryka_90's avatar
eryka_90
Helper I
1 year ago
Solved

Cumulative Value based on condition

Hi All,   I want to have cumulative value in my matrix table based on certain condition. Jan 2024 : Posting date < Feb 1st 2024 and clearing date > Jan 31st   Feb 2024 : Posting date < Mac 1st 20...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, eryka_90 

    You can try the following dax to achieve your need.

     

    Measure:

     

    Net Due = 
VAR _netDue =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            MONTH ( 'Table'[Net Due Date] ) - 1
                = MONTH ( SELECTEDVALUE ( 'Date'[firstDayofMonth] ) )
        )
    )
RETURN
    _netDue

0-15 = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[DaysDiff_1] <= 15
            && MONTH ( 'Table'[Net Due Date] )
                = MONTH ( SELECTEDVALUE ( 'Date'[firstDayofMonth] ) )
    )
)

16-31 = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[DaysDiff_1] < 31
            && 'Table'[DaysDiff_1] > 16
            && MONTH ( 'Table'[Net Due Date] )
                = MONTH ( SELECTEDVALUE ( 'Date'[firstDayofMonth] ) )
    )
)

DaysDiff_1 = 
VAR _netDueDate =
    SELECTEDVALUE ( 'Table'[Net Due Date] )
VAR _lastDayofMonth =
    EOMONTH ( _netDueDate, 0 )
VAR _dayDiff =
    INT ( _lastDayofMonth - _netDueDate )
RETURN
    _dayDiff

DaysDiff_2 = 
VAR _netDueDate =
    SELECTEDVALUE ( 'Table'[Net Due Date] )
VAR _lastDayofMonth =
    EOMONTH ( _netDueDate, 1 )
VAR _dayDiff =
    INT ( _lastDayofMonth - _netDueDate )
RETURN
    _dayDiff

     

     

    Best Regards,
    Yang
    Community Support Team

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know.     Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi, eryka_90 

It is possible to see the data you shared normally, but if you can share what you expect the visuals to look like as well as the logic it will help us realize your needs faster, thank you for your understanding. Feel free to help you.

Best Regards,
Yang
Community Support Team

eryka_90's avatar
eryka_90
Helper I
1 year ago

Hi Anonymous ,

 

Thank you for your reply. Let me brief you with an example and what I would like to achieve:

Example

Invoice NoPosting DateNet Due DateClearing Date
ABC8/19/20249/8/202411/5/2024
DEF10/1/202411/3/2024Blank

 

Expected Result

MonthNot Due0-1516-3131-60>60Explaination
Aug-2410000ABC : 8 days to due date from end of 31/8
Sep-2400100ABC : -22 days from 30/9
Oct-2410010ABC : -53 days from 31/10
DEF : 3 days to due fron end of 31/10
Nov-2401000ABC : cleared before monthly end 30/11
DEF : -27 days from 30/11
Dec-2400010DEF : -58 days from 31/12

 

You can refer to the explaination column on how the ageing should be worked. 

 

# CumulativeCount =
VAR CurrentMonthEnd = EOMONTH(MAX('DateTable'[Date]), 0)  // End of the current month from the DateTable context

RETURN
CALCULATE(
    DISTINCTCOUNT('Vendor Open Item'[Accouting Document Number]),  // Count distinct documents
    'Vendor Open Item'[Posting Date] <= CurrentMonthEnd,  // Include all documents posted in or before the current month
    OR(
        'Vendor Open Item'[Clearing Date] > CurrentMonthEnd,  // Include if cleared after the current month
        ISBLANK('Vendor Open Item'[Clearing Date])  // Include if not cleared yet
    )
)
 
I'm stuck with the aging bucket to achieve the expected result.
 
TQVM