Forum Discussion
Cumulative amount until EOM
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
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)