cancel
Showing results for
Did you mean:
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

## 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)
)```

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

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)
)```

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

Announcements

#### 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

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 163 members 1,660 guests
Recent signins: