Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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)
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
HI @shaunguyver
Please try this calculated measure
From last 30 days = CALCULATE( COUNTROWS('Table1') , FILTER( ALL('Table1'[Date Created]), 'Table1'[Date Created] >= TODAY() - 30 ) )
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)'?
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)
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |