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.
Hi,
im wondering that this topic is not found here.
I have a table with record ids, dates. This is related to an calendar table.
I just want to count the records which have a date from last month.
I tried:
Solved! Go to Solution.
@Anonymous
please try this
Measure =
VAR lastmonth=EOMONTH(today(),-2)+1
return CALCULATE(COUNT('Table'[record]),DATESBETWEEN('Table'[date],lastmonth,EOMONTH(lastmonth,0)))
Proud to be a Super User!
@Anonymous
if you only want to get an amount, you can try this.
Measure =
VAR lastmonth=edate(today(),-1)
return CALCULATE(COUNT('Table'[record]),DATESBETWEEN('Table'[date],lastmonth,EOMONTH(lastmonth,0)))
Proud to be a Super User!
Sorry, maybe i was confusing it.
i need to count all of the values from last month.(1st - last day of month)
According to your example it should count "4".
Regards
@Anonymous
please try this
Measure =
VAR lastmonth=EOMONTH(today(),-2)+1
return CALCULATE(COUNT('Table'[record]),DATESBETWEEN('Table'[date],lastmonth,EOMONTH(lastmonth,0)))
Proud to be a Super User!
Hi,
im very grateful 🙂 Now it works.
But im surprised that there is not just a more simple way to filter by relative .
Thanks for your help!
@Anonymous , You can try like
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))
diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Hi,
i tried already with
last month Sales = CALCULATE(Count(Sales[Sales Amount]),previousmonth('Date'[Date]))
There are no results. Previosmonth has my calendartable in it.
Maybe the issue is that im not using th measure in a table with dates.
I just want to show in a label the count of last month.
Regards
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |