Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
@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)
@setis Can you post sample data as text and expected output? Much easier to mock up a solution that way.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |