HELP !!! deadline approaching I cant filter the data in my table

[dw700d]

I have three calculations, Days remaining to spend( End date – current Date) Months Remaining to spend (End date – current month) and percentage complete (spend/amount).  I want to be able to filter these calculations by the Tracker column but I cant figure  out how. I have done the calculations in excel already I just need the ability to filter by tracker. I tried to list the contribution type twice in this table in an attempt to get the tracker filter to work but that inflates the numbers because its summing both rows. I tried to create two different tables but I couldnt establish a relationship. I am also filtering by contribution type which I am able to do with no trouble. Any one have suggestions?

Contribution Type	Amount	Date	Tracker	Days Remaining To Spend	Forecast Pacing Date	Months Remaining to Spend	percentage complete
Contribution 1	100,000	12/30/2019	55 Month Tracker	1463	12/31/2026	48	0.92%
Contribution 2	$250,000	6/29/2020	55 Month Tracker	1647	12/31/2026	54	0.92%
Contribution 3	67,000	12/31/2020	55 Month Tracker	1828	12/31/2026	60	0.92%
Contribution 1	100,000	12/30/2019	31 Month Tracker	732	12/31/2026	24	0.92%
Contribution 2	$250,000	6/29/2020	31 Month Tracker	916	12/31/2026	30	0.92%
Contribution 3	67,000	12/31/2020	31 Month Tracker	1097	12/31/2026	36	0.92%

[v-rzhou-msft]

Hi @dw700d 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your onedrive business.

Best Regards,

Rico Zhou

[v-rzhou-msft · Accepted solution]

Hi @dw700d 

Due to I don’t know what will the Slicer based on Tracker filter and what result you want, I build a table like yours and have a test.

Then I use calculated column to add columns.

Days Remaining To Spend = DATEDIFF('Start'[Date],'Start'[End date],DAY)

Months Remaining to Spend = DATEDIFF('Start'[Date],'Start'[End date],MONTH)

percentage complete = DIVIDE('Start'[Spend],'Start'[Amount])

Result is as below:

You can build a Slicer by another table like that:

Then build measure to calculate the value you want, measure is as below:

Measure =

var _select = SELECTEDVALUE(Slicer[Tracker])

return

IF(_select = "31 Month Tracker",SUM('Start'[Days Remaining To Spend]),0)

Result:

When I select 31 Month Tracker the visual will show the summing of Days Remaining To Spend.

When I select 55 Month Tracker the visual will show 0.

You can change two values in red box, and get different results as you want.

If this reply still couldn’t help you solve your problem, please provide me your date source and give me more details like what value you want the tracker filter or the screenshot of the result you want, and you can share your pbix file with me by your onedrive for business. And you can show me your calculate logic just like if I select 31 Month Tracker what result will the visual as below show, and the result when I select 55 Month Tracker.

You can download the pbix file from this link: deadline approaching I cant filter the data in my table

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@dw700d , looking at data shared you should be able to filter contribution type. Unless you used all in your measure. I am not able to visualize your challenge.

Can you share sample data and sample output in a table format?