Cumulative Value based on condition

[eryka_90]

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 2024 and clearing date > Feb 28st  

Mac 2024 : Posting date <Apr 1st 2024 and clearing date > Mac 31st 

DateTable has relationship with Vendor All Document using Posting Date

The measure as below:

CumulativeDocumentCount =

VAR CurrentMonthEnd = EOMONTH(TODAY(), 0)  // End of the current month

RETURN

CALCULATE(

    DISTINCTCOUNT('Vendor All Document'[Document Number]),

    'Vendor All Document'[Posting Date] <= CurrentMonthEnd,  // Posted in or before the current month

    OR(

        'Vendor All Document'[Clearing Date] > CurrentMonthEnd,  // Cleared after the current month

        ISBLANK('Vendor All Document'[Clearing Date])  // Still open

    ),

    'Vendor All Document'[Clearing Date] >= EOMONTH(TODAY(), -1) + 1 || ISBLANK('Vendor All Document'[Clearing Date])  // Exclude records cleared in the past

)

However, the return result was not showing cumulative value and its based on Month of Posting. Example January 2024 only shows value for document that has been posted on January 2024 only. Expected result should be all the document posted in January and before. How to achieve the result based on the condition?

You can refer to this link : Sample data 

TQVM

[Anonymous · Accepted solution]

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]

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]

Hi @Anonymous ,

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

Example

Invoice No	Posting Date	Net Due Date	Clearing Date
ABC	8/19/2024	9/8/2024	11/5/2024
DEF	10/1/2024	11/3/2024	Blank

Expected Result

Month	Not Due	0-15	16-31	31-60	>60	Explaination
Aug-24	1	0	0	0	0	ABC : 8 days to due date from end of 31/8
Sep-24	0	0	1	0	0	ABC : -22 days from 30/9
Oct-24	1	0	0	1	0	ABC : -53 days from 31/10 DEF : 3 days to due fron end of 31/10
Nov-24	0	1	0	0	0	ABC : cleared before monthly end 30/11 DEF : -27 days from 30/11
Dec-24	0	0	0	1	0	DEF : -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