Computing Overdue based on filtered dates

[jmcph]

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! 

[v-xicai · Accepted solution]

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.

[jmcph]

It actually solved my problem. Thank you very much! 

[Greg_Deckler]

@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.

[VijayP]

@jmcph 

I suggest to Create DateDim Table and connect with your FACT Table ,

Use Calculate along with SUMX .Then you will achieve. Currently you are dealing with dates within the fact Table it seems.