Forum Discussion

CG12's avatar
CG12
Frequent Visitor
7 years ago
Solved

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/462565
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 

 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    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?

1 Reply

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    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?