Count all entries last month

[Anonymous]

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:

Caluclate(count(Records,DATESINPERIOD('Calendar'[Date], TODAY(),-1, MONTH)))

But i do not get the right amount. i only get the values from the same date beginning last month

today 09. Oct.  and im getting all entries from last month beginning 09.Sept

Any help is welcome. 🙂

Thanks & regards

[ryan_mayu]

@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)))

[Anonymous]

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

[ryan_mayu · Accepted solution]

@Anonymous 

please try this

Measure = 
VAR lastmonth=EOMONTH(today(),-2)+1
return CALCULATE(COUNT('Table'[record]),DATESBETWEEN('Table'[date],lastmonth,EOMONTH(lastmonth,0)))

[Anonymous]

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!

[amitchandak]

@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.

[Anonymous]

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