Forum Discussion
Computing Overdue based on filtered dates
Hi,
I am trying to compute for Overdue balance using the this measure :
However, i encountered a problem with my monthly filters, when i pick a month that has no Collection, my measure doesnt seem to work. In the picture below, i filtered to December 2018 which shows no result (which i expect to return a 800 Overdue value).
When i filtered in to Nov 2018, it works just fine. The last Collection was Jan 2019, and when selected, it works as well. I inserted my measures for the Lastcollectdate and Maturity date which i think caused my problem
Thank you for taking time on this query! Feel free to correct me and I hope that you can help me with this!
Hi jmcph ,
You may change your formula like DAX below, which use the ALLSELECTED function to take effect of slicer filter, do the same with measure [MatDate] to replace ALL function.
Overdue=Sumx(ALLSELECTED(Masterlist), if…)In addition, you may create Calendar table , and use it in your formula which need to use time intelligence. For example, you may use " DATEADD(Calendar[Date], 60, DAY) " instead of " Masterlist[Date]+60"
Calendar= CALENDARAUTO()Or could you share some data sample and expected output? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
How to Get Your Question Answered Quickly
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
jmcph Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Hi jmcph ,
You may change your formula like DAX below, which use the ALLSELECTED function to take effect of slicer filter, do the same with measure [MatDate] to replace ALL function.
Overdue=Sumx(ALLSELECTED(Masterlist), if…)In addition, you may create Calendar table , and use it in your formula which need to use time intelligence. For example, you may use " DATEADD(Calendar[Date], 60, DAY) " instead of " Masterlist[Date]+60"
Calendar= CALENDARAUTO()Or could you share some data sample and expected output? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
How to Get Your Question Answered Quickly
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It actually solved my problem. Thank you very much!
