cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vnl3
Frequent Visitor

how to establish starting values based on the same month each year to be counted down

I am working with canceling grant monies that were not utilized in the fiscal year that they were issued. The fiscal year begins 1 October. The number of grants that must be canceled are determined at the end of the previous fiscal year (30 Sept) and the number of grants and the funding amounts must be drawn down to zero by the end of the current fiscal year. Here's what I tried:

 

CancelingUniverseCount2020 = CALCULATE(DISTINCTCOUNT(CancelingFundsMonthlyReport[Grant Number]),CancelingFundsMonthlyReport[Report Date]=FILTER(CancelingFundsMonthlyReport,FIRSTDATE(CancelingFundsMonthlyReport[Report Date])))
 
The error message I get is "A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
 
Please help.
1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

Re: how to establish starting values based on the same month each year to be counted down

Hi , @vnl3 

Here is a demo.

Pbix attached 

 

If help ,please refer these steps.

1. Create column as below:

 

Report Date2 = VALUE( FORMAT(CancelingFundsMonthlyReport[Report Date],"yyyymm"))

 

2. Create two measures as below

 

Grant measure1 = 
var month = SELECTEDVALUE(CancelingFundsMonthlyReport[Report Date2])
var b= CALCULATE(
	DISTINCTCOUNT('CancelingFundsMonthlyReport'[Grant Number]),
	FILTER(
        ALL('CancelingFundsMonthlyReport'),
        CancelingFundsMonthlyReport[Report Date2]=month
))
var c=CALCULATE(
	DISTINCTCOUNT('CancelingFundsMonthlyReport'[Grant Number]),
	FILTER(
        ALL('CancelingFundsMonthlyReport'),
        CancelingFundsMonthlyReport[Report Date2]=month-1
))
return  IF(c=BLANK(),BLANK(),c-b)
Funding Measure2 = 
var month = SELECTEDVALUE(CancelingFundsMonthlyReport[Report Date2])
var b= CALCULATE(
	SUM('CancelingFundsMonthlyReport'[Funding]),
	FILTER(
        ALL('CancelingFundsMonthlyReport'),
        CancelingFundsMonthlyReport[Report Date2]=month
))
var c=CALCULATE(
	SUM('CancelingFundsMonthlyReport'[Funding]),
	FILTER(
        ALL('CancelingFundsMonthlyReport'),
        CancelingFundsMonthlyReport[Report Date2]=month-1
))

return IF(c=BLANK(),BLANK(),c-b)

 

It will show as below.

107.png

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Highlighted
JoseCruzCat Helper I
Helper I

Re: how to establish starting values based on the same month each year to be counted down

Hello,

Filter function must to be used to compare an expression, not a column, this means:

 

CancelingUniverseCount2020 = CALCULATE(DISTINCTCOUNT(CancelingFundsMonthlyReport[Grant Number]),FILTER(CancelingFundsMonthlyReport[Report Date]=FIRSTDATE(CancelingFundsMonthlyReport[Report Date]))

 

Hope this helps!!

Regards 

Super User IV
Super User IV

Re: how to establish starting values based on the same month each year to be counted down

First, 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

 

Second, it's somewhat hard to tell what is going on there but try this:

 

CancelingUniverseCount2020 = 
COUNTROWS(
	DISTINCT(
		SELECTCOLUMNS(
			FILTER(
				CancelingFundsMonthlyReport,
				FIRSTDATE(CancelingFundsMonthlyReport[Report Date])
			)
			"__Grant Number",CancelingFundsMonthlyReport[Grant Number]),
		)
	)
)

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

vnl3
Frequent Visitor

Re: how to establish starting values based on the same month each year to be counted down

Recommendation?

vnl3
Frequent Visitor

Re: how to establish starting values based on the same month each year to be counted down

I'm running into two issues with your recommendation: the count is off by +1 and the values change in the visualizations when the date filters are applied.

vnl3
Frequent Visitor

Re: how to establish starting values based on the same month each year to be counted down

For example, at the end of FY 19 (Sep 2019) there were 3 grants with funding that were not expended and can no longer be utilized. Therefore, they all must  be canceled in FY 20 (beginning in Oct 19). I need to show the countdown progress for each month based on the total count of grants left in Sep. So, in October zero grants had been canceled but in November one grant or 33% (1/3) was canceled since grant "123" is no longer showing for that month. I will also need to conduct the same analysis for the funding amount. So November should show $15 as being canceled or 50% ($15/$30) progress.

 

I hope this clarifies my inquiry.

 

GrantFundingDate
ABC5Sep 2019
12315Sep 2019
78910Sep 2019
ABC5Oct 2019
12315Oct 2019
78910Oct 2019
ABC5Nov 2019
78910Nov 2019
Microsoft
Microsoft

Re: how to establish starting values based on the same month each year to be counted down

Hi , @vnl3 

Here is a demo.

Pbix attached 

 

If help ,please refer these steps.

1. Create column as below:

 

Report Date2 = VALUE( FORMAT(CancelingFundsMonthlyReport[Report Date],"yyyymm"))

 

2. Create two measures as below

 

Grant measure1 = 
var month = SELECTEDVALUE(CancelingFundsMonthlyReport[Report Date2])
var b= CALCULATE(
	DISTINCTCOUNT('CancelingFundsMonthlyReport'[Grant Number]),
	FILTER(
        ALL('CancelingFundsMonthlyReport'),
        CancelingFundsMonthlyReport[Report Date2]=month
))
var c=CALCULATE(
	DISTINCTCOUNT('CancelingFundsMonthlyReport'[Grant Number]),
	FILTER(
        ALL('CancelingFundsMonthlyReport'),
        CancelingFundsMonthlyReport[Report Date2]=month-1
))
return  IF(c=BLANK(),BLANK(),c-b)
Funding Measure2 = 
var month = SELECTEDVALUE(CancelingFundsMonthlyReport[Report Date2])
var b= CALCULATE(
	SUM('CancelingFundsMonthlyReport'[Funding]),
	FILTER(
        ALL('CancelingFundsMonthlyReport'),
        CancelingFundsMonthlyReport[Report Date2]=month
))
var c=CALCULATE(
	SUM('CancelingFundsMonthlyReport'[Funding]),
	FILTER(
        ALL('CancelingFundsMonthlyReport'),
        CancelingFundsMonthlyReport[Report Date2]=month-1
))

return IF(c=BLANK(),BLANK(),c-b)

 

It will show as below.

107.png

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Microsoft
Microsoft

Re: how to establish starting values based on the same month each year to be counted down

Hi , @vnl3 

Could you please tell me whether your problem has been solved?
If it is,  please mark the helpful replies or add your reply as Answered to close this thread.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors