Total till last month

[Anonymous]

Hi ,

I need a measure total approved status of my ID till last calander month.

Created Date	IDs	Status
01/01/2020	ABCA123	Approved
01/03/2020	ABCD456	Rejected
02/01/2020	123455	Approved
02/02/2020	HAABA	Rejected
04/01/2020	123644	Approved
06/15/2020	AAA	Approved
06/30/2020	BBB	Approved
07/01/2020	CCC	Rejected
07/05/2020	DDD	Approved

[ryan_mayu · Accepted solution]

@Anonymous 

you can try this

Measure = 
VAR lastmonth= EOMONTH(date(year(TODAY()),month(TODAY())-1,1),0)
return CALCULATE(COUNTROWS(Sheet6),FILTER(Sheet6,Sheet6[Created Date]<=lastmonth&&Sheet6[Status]="Approved"))

[amitchandak]

@Anonymous , something like this with date table

Cumm Sales = CALCULATE(countrows(Table),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,Month))), table[Status] = "Approved")

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

also refer example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

[AilleryO]

Hi,

You can use a FILTER() function to filter only Approved lines, and STARTOFMONTH(TODAY())-1 to get the last calendar month.

I'm not sure about your requirement about IDs, but a filter function maybe with a SELECTEDVALUE should do the trick.

If you want a more specific answer you should send us more details about your data.