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.
Hi,
I have a BI Visual, in which I am visualizing Dues per month, but I want to see the cumulative sum of the current month + previous months dues
Due Receivables =
CALCULATE(
'Account Receivables'[Total Receivables],
USERELATIONSHIP(Receivables[DueDate],'Date'[Date]),
FILTER(
PaymentStatus,
PaymentStatus[Code] = 17),
Receivables[OverDueCol] >= 1
)
In Feburary 2022, it should show Feburary Dues + Last months dues which are visible.
in Feburary 2022, the Sum Should be ( 132,266,913.31), I am unable to do this @amitchandak @Anonymous
@MH3 , Eveny month due is related with last month then it need to cummulative
example
A-B both till this month =
CALCULATE(SUM(Table[Reecived]),filter(date,date[date] <=max(date[date]))) - CALCULATE(SUM(Table[paid]),filter(date,date[date] <=max(date[date])))
A -B , B till last month
CALCULATE(SUM(Table[Reecived]),filter(date,date[date] <=max(date[date]))) - CALCULATE(SUM(Table[paid]),filter(date,date[date] <=eomonth(max(date[date]),-1) ))
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
@amitchandak Actually,
My Target is to show the OverDue dues, of last months and the current month (Feb) according to the filter context
in Feburary, it should be the sum of previous months Dues and Feb dues itself
@MH3 , You can get the previous month using the date table and time intelligence
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi @amitchandak
I used your approach but still not getting desired result, I really need help
CALCULATE (
'Account Receivables'[Total Receivables],
DATESMTD ( ENDOFMONTH ( DATEADD ( 'Date'[Date], -1, MONTH ) ) ),
PaymentStatus[Code] = 17,
Receivables[OverDueCol] >= 1
)
it Shows wrong results for Feburary (which should be) (132,266,913)
@MH3 ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi @MH3
I've checked the excel you shared, but not very sure. Do you want to get the cumulative dues in each month?
For example, is the red part you need? (I just use it as an example. After determining the logic you need, I will use your data to solve the problem.)
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi yes exactly like that, because the invoices are dues till feburary and they should show the commulative sum till feburary
till feburary it should show (which should be) (132,266,913)) as per the Excel sheet.
I did some calculations but my calculation is including months which don't even have any dues. it just sums all months till the 2022 feburary.
Hi @MH3
I figure it out, please check the outcome below. Try that measure in your file, their calculation logic is the same.
test = CALCULATE(SUM('Table'[Due Receivables]),FILTER(ALL('Table'),'Table'[MonthYear]<=MIN('Table'[MonthYear])))
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xiaotang
I have tried your formula, but the result is kinda wrong in WaterFall chart
The same WaterFall Chart on top shows overdue amount per month,
But I want to commulative sum till Feb 2022, the new Sum is ( 106, 027, 215 ) till Feburary 2022
test = CALCULATE('Account Receivables'[Total Receivables],
FILTER(
ALL('Date'),
'Date'[MonthYear] <= MIN('Date'[MonthYear])
),
PaymentStatus[Code] = 17 \\ Shows Pending Invoices
,
Receivables[OverDueCol] >= 1 \\ Those amounts who are OverDue by 1 day or more
)
Hi @amitchandak
https://drive.google.com/file/d/1sCm8emVnJcLySdLBqoUx9F7J0jSVeIZU/view?usp=sharing
Here is my Drive Link, the Data for the report is in excel and I need to show in the Water fall chart
The Feburary should show the amount, from its previous months and including itself
in Feburary 2022, the Sum Should be ( 132,266,913.31)
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 |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |