cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
georgec96
Helper I
Helper I

Count rows where date column is 1st day of next month

Hi all, im trying to create a measure that would count all rows where the column date is 1st day of next month however im getting an error when using the CALCULATE function.

 

"A function "CALCULATE" has been used in a true or false expression that is used as a table filter expression. This is not allowed."

 

Thank you in advance

11 REPLIES 11
tamerj1
Community Champion
Community Champion

Hi @georgec96 

please try

 

 

DK orders Left =
VAR NextMonth =
    EOMONTH ( ebs_lcl_open_lines[order_due_date], 0 ) + 1
RETURN
    CALCULATE (
        COUNT ( ebs_lcl_open_orders[oracle_order_number] ),
        ebs_lcl_open_orders[due_date] = NextMonth
    )

 

 

Hi @tamerj1 

 

Can I write this as a measure?

 

It won't let me reference the datecolumn when i try.

georgec96_0-1655976630151.png

 

tamerj1
Community Champion
Community Champion

@georgec96 
Yes you can

DK orders Left =
VAR NextMonth =
    EOMONTH ( MAX ( ebs_lcl_open_lines[order_due_date] ), 0 ) + 1
RETURN
    CALCULATE (
        COUNT ( ebs_lcl_open_orders[oracle_order_number] ),
        ebs_lcl_open_orders[due_date] = NextMonth
    )

Hi @tamerj1 , thanks for your reply

 

However my measure is returning a blank value

 

georgec96_1-1655979567873.png

 

tamerj1
Community Champion
Community Champion

@georgec96 

Yes as there is no data for next month. You need to slice by or select date

@tamerj1 

 

column due_date containts the date 1st of july 2022 which is what I want to count.

 

I was hoping that the measure will return a count of all those rows where the due_date is 1st of july 2022

tamerj1
Community Champion
Community Champion

@georgec96 

Ok then let's return nextmonth 

DK orders Left =
VAR NextMonth =
    EOMONTH ( MAX ( ebs_lcl_open_lines[order_due_date] ), 0 ) + 1
VAR Result =
    CALCULATE (
        COUNT ( ebs_lcl_open_orders[oracle_order_number] ),
        ebs_lcl_open_orders[due_date] = NextMonth
    )
RETURN
    NextMonth
tamerj1
Community Champion
Community Champion

@georgec96 
How does your data look like?

tamerj1
Community Champion
Community Champion

@Hi @georgec96 

what code did you use?

@tamerj1 

 

georgec96_0-1655979745429.png

 

Hi @tamerj1  I have used the following formula 

 

DK orders Left = CALCULATE(COUNT(ebs_lcl_open_orders[oracle_order_number]),ebs_lcl_open_orders[due_date]=NEXTMONTH(ebs_lcl_open_lines[order_due_date]))

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors