Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
Greg_Deckler
Super User
Super User

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]),
		)
	)
)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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

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.

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.

Anonymous
Not applicable

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 

Recommendation?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.