Count total items created in last month

[shaunguyver]

I have a simple table, with two columns:

Column 1 - Value

Column 2 - Date Created

I want to create a measure which displays to total number of items in my table created in the last 30 days. I also want to create a measure that does the same, but for the last 90 days. How do I do this?

Thanks

[ChristianRHouen]

Hi

Great formula, @Phil_Seamark !

I tried to get last month (i.e. if current month is august then count July), but using the month(today())-1 method didn't work as it was influenced by same month previous years. 

Is there a way to circumvent this?

Kind regards

[Phil_Seamark]

HI @shaunguyver

Please try this calculated measure

From last 30 days = 
    CALCULATE(
            COUNTROWS('Table1') , 
            FILTER(
                ALL('Table1'[Date Created]),
                'Table1'[Date Created] >= TODAY() - 30
                )
            )

[shaunguyver]

Thanks Phil - that's worked great. One more question - if the measure returns no values, is there any way of delivering a '0' result rather than '(Blank)'?

[Phil_Seamark · Accepted solution]

Please try this tweak to return a 0

From last 30 days = 
VAR x = CALCULATE(
            COUNTROWS('Table1') , 
            FILTER(
                ALL('Table1'[Date Created]),
                'Table1'[Date Created] >= TODAY() - 30
                )
            )
RETURN IF(x > 0, x, 0)         

[Anonymous]

Hi @Phil_Seamark 

I tried your formula, modified it and the current solution works. The only "problem" I have is that it is not fully dynamic.

CustomersCreatedLastMonth = 
    CALCULATE(
             COUNTROWS(Customers);
             FILTER(
                      ALL(Customers[CustomerCreatedDate]);
                      Customers[CustomerCreatedDate]>=DATE(YEAR(TODAY());MONTH(TODAY()-30);1)
                   )
             )

E.g if TODAY() = 2019-10-31, then my DATE formula would treat this as OCT and the date would be 219-10-01. So it wouldn't take the last months first day. 
Even worse if we are talking about February. Would in any case track back to January.

@Phil_Seamark Any ideas how to make it dynamic, so it would take last month and wouldn't be hardcoded in the formula.

Many thanks
Rami

[Anonymous]

CustomersCreatedLastMonth = 
                    CALCULATE(
                          COUNTROWS(Customers);
                          FILTER(
                                     ALL(Customers[CustomerCreatedDate]);
                                     Customers[CustomerCreatedDate]>= DATE(YEAR(TODAY());MONTH(TODAY())-1;1)))

I actually solved my problem. Had the deduction a bit too early. Now the code is dynamic.

[shaunguyver]

Awesome, thank you Phil!