Get # of years, excluding current month

[Unicorn_Tech]

Hello.  I have the following calculated table.  I use "Count of Years" to divide by the sum of page views to get an average for the relevant month. 

I would like the "Count of Years" to exclude the current month, so rather than recording 3 years for Feburary, I would like it to record 2, since we still haven't completed the month.  Is there a way to do that?

Avg Rating = Summarize(
    'All Web Site Data','All Web Site Data'[Month Number],
    "Sum per month",
    SUM('All Web Site Data'[Pageviews]),
    "Count of Years", Calculate(DISTINCTCOUNT('All Web Site Data'[Year]))
)

[ryan_mayu]

@Unicorn_Tech 

could you pls provide the sample data and expected output

[Unicorn_Tech]

Hi @ryan_mayu ,  If you see the table below, the average monthly view here is including Feb. 2023 data, I'd rather it just include 2022 and before, since we still have the month.

"Month Number" in my original table is: 

Month Number = month('All Web Site Data'[Date])

The calculated table is:

Avg Rating = Summarize(
    'All Web Site Data','All Web Site Data'[Month Number],
    "Sum per month",
    SUM('All Web Site Data'[Pageviews]),
    "Count of Years", Calculate(DISTINCTCOUNT('All Web Site Data'[Year]))
)
    

With an "Average Monthly View" column of 

Average Monthly View = 'Avg Rating'[Sum per month] / 'Avg Rating'[Count of Years]

[ryan_mayu · Accepted solution]

@Unicorn_Tech 

i am not sure what's the best solution for this.

here is a workaround. maybe you can try to create a column

Column = if(year('Table'[date])=year(today())&&month('Table'[date])=month(today()),"No")

then filter out the data with "no" value in the visual.

[Unicorn_Tech]

Thank you @ryan_mayu , that worked.

[ryan_mayu]

you are welcome

[DvdH]

Create a measure which is simply month number - 1 and use this in the function instead of month number.