I will show you different ways to calculate something based on date, for instance, I want to know how many incidents I have on March or on 2022 or on March and 2022, etc. Imagine that I have this data:

Before to start to create measures, I will add 2 new columns:

• For the column “month”, I put this formula: FORMAT([argument],"YYYY-MM")
  
• For the column “monthnum”: YEAR('table'[argument])*12+ROUNDUP(MONTH('table'[argument]),0)
  

NOTE: if you want to have the month name, just replace YYYY-MM by MMMM, and as you may guess, if you want to have a year column, use the same formula as the month one but put only YYYY. If you are looking about quarter, read my article Power BI: calculate based on automatic dates (quarters, months).

I will create a measure to calculate the number of P1 for January 2023:

CALCULATE(COUNT('table'[argument1]),'table'[argument2]="XX" && 'table'[argument3]="XX")

Now imagine that January 2023 is my current month, I can use this formula:

CALCULATE(COUNT('table'[argument1]),'table'[argument2]="XX" && MONTH('table'[argument3])=MONTH(NOW()))

Let’s take a look about the results:

As you can see, measure 1 gives me the correct result. The measure 2 is not correct because it should be 2, the reason is that using “MONTH(NOW())”, it is counting all the current months, which means all January, without taking in account the year. To remediate it, there are 2 options. The first one is to add the year:

CALCULATE(COUNT('table'[argument1]),'table'[argument2]="XX" && MONTH('table'[argument3])=MONTH(NOW()) && YEAR('table'[argument4])=YEAR(NOW()))

The second option is to use a month filter:

CALCULATE(CALCULATE(COUNT('table'[argument1]),'table'[argument2]="XX"),FILTER(ALL('table'),
'table'[argument3]=MAX('table'[argument3])))

Here the results:

Now I want to know how many P1 for the previous month, since in this scenario, I am in January 2023, I want to know for December 2022. I can use the formula of the measure 1 but the best is to find a way to do it automatically without changing the value every year. I can’t use the ones above because it will give me an error by using the “month” column, in such situation, I will use the “monthnum” column:

CALCULATE(CALCULATE(COUNT('table'[argument1]),'table'[argument2]="XX"),FILTER(ALL('table'),
'table'[argument3]=MAX('table'[argument3])-1))

As you can see, at the end of the formula, there is “-1”, if I remove it or change 1 by 0, it will give me the result of January 2023 and if I change 1 by 2, it will give me the result of November 2022, etc. To resume, this formula allows to know the result of the past months.

For the first option, alternatively, I can use the year filter to get the same result:

CALCULATE(CALCULATE(COUNT('table'[argument1]),'table'[argument2]="XX" && MONTH('table'[argument3])=MONTH(NOW())),FILTER(ALL('table'),'table'[argument4]=MAX('table'[argument4])))

For both, if I want to know Januray (current month = “MONTH(NOW())”) for the past years, I will add “-1” or “-2” and so on at the end of the formula:

Lastly, if I want to calculate for the year only, as you may guess, the formula is the same as above by just removing the month:

In both examples, it is calculating the previous year.