cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CG12 Frequent Visitor
Frequent Visitor

Filter by Current Month and Last Month using Calculated Column

Hi Folks,

 

This has been a headscratacher for me. Basically I am trying to get a frequency count of a particular column from another table but only from within a particular month (kind of dynamic) - Current Month, Last Month, Month previous to that. 

 

Based on research I tried to do the following 
https://community.powerbi.com/t5/Desktop/Current-Month-Previous-Month-and-Before-Previous-Month/td-p...
To give me the following -

FrequencyCurrentMonth = CALCULATE(COUNT('Table1'[Prospect]),RELATEDTABLE('Table1'),FILTER(ALL('Table1'),MONTH('Table1'[DateTime])=MONTH(TODAY())))

 

This gave me a blank values with no return. 

 

Was wondering if you can advice or is there something I can use?

 

Thanks 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Filter by Current Month and Last Month using Calculated Column

Hello @CG12 

Your best bet in the time intelligent function DATESINPERIOD.  You will need a date table but then your calculation is just something like:

3 MO Prospect = 
VAR EndingDate = MAX ( Dates[Date] )
RETURN 
CALCULATE(
    [Prospect],
    DATESINPERIOD(Dates[Date],EndingDate,-3,MONTH)
)

3MoProspect.jpg
Could you maybe share a sample of your data model if the above doesn't work for you?

1 REPLY 1
Super User
Super User

Re: Filter by Current Month and Last Month using Calculated Column

Hello @CG12 

Your best bet in the time intelligent function DATESINPERIOD.  You will need a date table but then your calculation is just something like:

3 MO Prospect = 
VAR EndingDate = MAX ( Dates[Date] )
RETURN 
CALCULATE(
    [Prospect],
    DATESINPERIOD(Dates[Date],EndingDate,-3,MONTH)
)

3MoProspect.jpg
Could you maybe share a sample of your data model if the above doesn't work for you?

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 163 members 1,660 guests
Please welcome our newest community members: