Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table that has Ratified Dates, so I want to count the number of Ratified Dates that have a date in them and by current month, or by specfic Month. For example, there are 3 Ratified dates in September for a specific client Abbreviation, then count those number of times there is a date for September.
Solved! Go to Solution.
Hi @Kornholio
Try this: PBIX 10/20/2019
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Ratified Dates All = VAR _minDate = MIN ( Con2[RatifiedDates] ) VAR _maxDate = MAX ( Con2[RatifiedDates] ) VAR _calc = CALCULATE ( [Countrows2], FILTER ( ALLEXCEPT ( Con2, Con2[DevelopmentCode] ), Con2[RatifiedDates] >= _minDate && Con2[RatifiedDates] <= _maxDate ) ) RETURN _calc
Proud to be a Super User!
Proud to be a Super User!
Hi @Kornholio ,
Can you provide us with a pbix, or some dummied up tables that show your tables, and where you want to go.
Thanks,
Nathaniel
Please read this post to get your question answered more quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Proud to be a Super User!
COMPANYCODE | DEVELOPMENTCODE | RELEASE_DATE | JIONUMBER | UNUSED | COSTFLAG | SALESRELEASEDATE | CONTRACT_DATE | RATIFIED_DATE |
1 | AG | Wednesday, November 2, 2016 | X | Friday, January 27, 2017 | Friday, January 27, 2017 | |||
1 | AG | Wednesday, May 31, 2017 | X | Tuesday, April 17, 2018 | Tuesday, April 17, 2018 | |||
1 | AG | Wednesday, November 2, 2016 | Y | Sunday, September 15, 2019 | Sunday, September 15, 2019 | |||
1 | AG | Monday, May 22, 2017 | Y | Sunday, September 1, 2019 | Sunday, September 1, 2019 | |||
1 | AG | Thursday, June 8, 2017 | X | Thursday, February 21, 2019 | Thursday, February 21, 2019 | |||
1 | AG | Wednesday, May 31, 2017 | X | Thursday, February 22, 2018 | Thursday, February 22, 2018 | |||
1 | AG | Y | ||||||
1 | AG | Y | ||||||
1 | AG | Wednesday, May 29, 2019 | Y | |||||
1 | AG | Y | ||||||
1 | AG | Monday, February 26, 2018 | X | Monday, December 18, 2017 | Monday, December 18, 2017 | |||
1 | AG | Monday, April 17, 2017 | X | Thursday, March 31, 2016 | Thursday, March 31, 2016 | |||
1 | AG | Y | ||||||
1 | AG | Tuesday, March 21, 2017 | Y | |||||
1 | AG | Y | ||||||
1 | AG | Y | ||||||
1 | AG | Wednesday, November 2, 2016 | X | Friday, November 4, 2016 | Friday, November 4, 2016 | |||
1 | AG | Wednesday, May 3, 2017 | X | Friday, April 7, 2017 | Friday, April 7, 2017 | |||
1 | AG | Wednesday, November 2, 2016 | X | Wednesday, March 30, 2016 | Monday, November 28, 2016 | |||
1 | AG | Wednesday, November 2, 2016 | X | Sunday, January 22, 2017 | Sunday, January 22, 2017 | |||
1 | AG | Wednesday, November 2, 2016 | X | Thursday, October 27, 2016 | Tuesday, November 29, 2016 | |||
1 | AG | Y |
So using the Development Code, count the number of times in September of 2019 that there was a date
So count the number of times that in september that there was a Ratified Date for Development code AG
As you can see in September there are 2 dates filled, so i need to show for current month, September in my data set of AG=2 for count of times Ratified date was filled
Are you responding or did we post at the same time?
Proud to be a Super User!
@Kornholio ,
Note that I put in the specific for Sept, but not for the code, as there is only one code. We can change that if you want, but right now the ALLEXCEPT() will restart the count everytime there is a new code. Here is my pbix PBIX
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Ratified Dates in Sept for Dev Code = VAR _Septmonth = MONTH ( MAX ( Con[RATIFIED_DATE] ) ) VAR _calc = CALCULATE ( COUNTA ( Con[RATIFIED_DATE] ), ALLEXCEPT ( Con, Con[DEVELOPMENTCODE] ), MONTH ( ( Con[RATIFIED_DATE] ) ) = _Septmonth ) RETURN _calc
Proud to be a Super User!
I tried that in this PBI Example and it shows way more. I have atttached the PBX
Can i attach my pbx and how?
@Kornholio wrote:I tried that in this PBI Example and it shows way more. I have atttached the PBX
You save your pbix on one drive, or drop box. Then copy the link and paste it using the hypelink symbol between the smiley face and the camera. Do you want me to limit it to the one company? I need to go off line in about 15 min.
Proud to be a Super User!
going to dropbox it now
let me know if this worked for dropbox
Hi @Kornholio ,
Have to go, here is my pbix. This responds to the slicer for the code, but not the date. I will come back to work on this - not this weekend.
Nathaniel
Proud to be a Super User!
HI, but if i look at "BM" Development Code I see there is only 1 Ratified Date for September, and on your PBX it shows 2 when it should show 1
Hi @Kornholio
Try this: PBIX 10/20/2019
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Ratified Dates All = VAR _minDate = MIN ( Con2[RatifiedDates] ) VAR _maxDate = MAX ( Con2[RatifiedDates] ) VAR _calc = CALCULATE ( [Countrows2], FILTER ( ALLEXCEPT ( Con2, Con2[DevelopmentCode] ), Con2[RatifiedDates] >= _minDate && Con2[RatifiedDates] <= _maxDate ) ) RETURN _calc
Proud to be a Super User!
Although, if i wanted to count current month, without having to choose the date filter, is that possible? So, I will have multple counts, each one will be for current month when opening the pbx. So, without having to choose any date filter, can we use your calc with the current month when opening the pbx?
Hi @Kornholio ,
I created two pages with the opening page having current month only. You could use a bookmark and a button if you wanted to get to second page or leave it as is. This will only work for the current month. It uses MONTH() and TODAY()
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Current Month = var _currentMonth = Month(TODAY()) //takes today and selects the month, then applies it to the filter below VAR _calc = CALCULATE ( [Countrows2], FILTER ( ALLEXCEPT ( Con2, Con2[DevelopmentCode] ), Month(Con2[RatifiedDates]) = _currentMonth ) ) RETURN _calc
Proud to be a Super User!
Proud to be a Super User!
I think this will work thanks!
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |