Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to Solution.
Hi , @vnl3
Here is a demo.
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.
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.
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]),
)
)
)
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.
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.
Grant | Funding | Date |
ABC | 5 | Sep 2019 |
123 | 15 | Sep 2019 |
789 | 10 | Sep 2019 |
ABC | 5 | Oct 2019 |
123 | 15 | Oct 2019 |
789 | 10 | Oct 2019 |
ABC | 5 | Nov 2019 |
789 | 10 | Nov 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.
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.
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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |