Forum Discussion
Cumulative amount until EOM
Dear experts,
I have a measure that calculates the overdue amount of all cases with a "Due date" on the selected month.
It looks like this:
Overduev2.1 =
CALCULATE(
SUMX(
FILTER('ALL$Cust_ Ledger Entry', 'ALL$Cust_ Ledger Entry'[Due Date] <= TODAY()
&& (ISBLANK('ALL$Cust_ Ledger Entry'[Posting date DCLE])||
'ALL$Cust_ Ledger Entry'[Posting date DCLE] > 'ALL$Cust_ Ledger Entry'[Due Date])), [OpenSales]))
The results are fine.
There is an active relationship between ALL$Cust_ Ledger Entry'[Due Date] and my Date table on 'Date[date]
What I'm trying to obtain is the cumulative overdue amount until the end of every month, so if there is an amount that is overdue in January and it's still overdue in February, the amount should show up in both months.
My attempt is this but it's wrong:
Overduev2.2 =
CALCULATE(
SUMX(
FILTER('ALL$Cust_ Ledger Entry', 'ALL$Cust_ Ledger Entry'[Due Date] <= TODAY()
&& (ISBLANK('ALL$Cust_ Ledger Entry'[Posting date DCLE])||
'ALL$Cust_ Ledger Entry'[Posting date DCLE] > 'ALL$Cust_ Ledger Entry'[Due Date])), [OpenSales]),
FILTER(ALLSELECTED('Date'),
'Date'[Date] <= 'Date'[Calendar EndOfMonth]))
The column 'Date'[Calendar EndOfMonth] is just a calculated column returning the last date of the month.
Thank you in advance!
setis , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Date table should be joined with Due date and try like
Overduev2.2 =
CALCULATE(
SUMX(
FILTER('ALL$Cust_ Ledger Entry', (ISBLANK('ALL$Cust_ Ledger Entry'[Posting date DCLE])||
'ALL$Cust_ Ledger Entry'[Posting date DCLE] > 'ALL$Cust_ Ledger Entry'[Due Date])), [OpenSales]),
FILTER(ALLSELECTED('Date'),
'Date'[Date] <= max('Date'[Date]) && 'Date'[Date] <=today())if there is due and it need to be carried fwd this should work
4 Replies
setis Can you post sample data as text and expected output? Much easier to mock up a solution that way.
setis , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Date table should be joined with Due date and try like
Overduev2.2 =
CALCULATE(
SUMX(
FILTER('ALL$Cust_ Ledger Entry', (ISBLANK('ALL$Cust_ Ledger Entry'[Posting date DCLE])||
'ALL$Cust_ Ledger Entry'[Posting date DCLE] > 'ALL$Cust_ Ledger Entry'[Due Date])), [OpenSales]),
FILTER(ALLSELECTED('Date'),
'Date'[Date] <= max('Date'[Date]) && 'Date'[Date] <=today())if there is due and it need to be carried fwd this should work
Greg_Deckler and amitchandak thanks for your answers.
amitchandak your suggestion does exactly what I was looking for. Thanks!
amitchandak and Greg_Deckler I'm sorry, I was too quick saying that this worked. Here is a dummy table trying to reproduce the issue and here is a file with the table: https://drive.google.com/file/d/1lGVOjPH3o2sLd0Lk9InO9IBgrligCvjH/view?usp=sharing
Doc NrDue DatePosting dateAmount
101 02-05-2019 05-07-2019 500 102 07-06-2019 12-06-2019 200 103 09-08-2019 05-05-2020 800 What I intend to show is the amount for the Doc Nr 101 carried from the May-2019 to June-2019 (or from due date to posting date if the granularity is at day level)
