Count Non Blank Dates for current month

[Kornholio]

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.

[Nathaniel_C · Accepted 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

[Nathaniel_C]

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

[Kornholio]

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

[Kornholio]

So using the Development Code, count the number of times in September of 2019 that there was a date

[Kornholio]

So count the number of times that in september that there was a Ratified Date for Development code AG

[Kornholio]

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

[Nathaniel_C]

Are you responding or did we post at the same time?

[Nathaniel_C]

@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

[Kornholio]

I tried that in this PBI Example and it shows way more. I have atttached the PBX

[Kornholio]

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

---

[Nathaniel_C]

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.

[Kornholio]

going to dropbox it now

[Kornholio]

https://www.dropbox.com/s/ddxi42kzibc1jaw/Master%20Test%20PBI.pbix?dl=0

[Kornholio]

let me know if this worked for dropbox

[Nathaniel_C]

PBIX 

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

[Kornholio]

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

[Nathaniel_C · Accepted 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

[Kornholio]

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?

[Nathaniel_C]

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

[Nathaniel_C · Accepted solution]

PBIX 

[Kornholio]

I think this will work thanks!