Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Anonymous
Not applicable

Total till last month

Hi ,

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

Created DateIDsStatus
01/01/2020ABCA123Approved
01/03/2020ABCD456Rejected
02/01/2020123455Approved
02/02/2020HAABARejected
04/01/2020123644Approved
06/15/2020AAAApproved
06/30/2020BBBApproved
07/01/2020CCCRejected
07/05/2020DDDApproved

 

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
ryan_mayu
Super User
Super User

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
Super User

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
AilleryO
Memorable Member
Memorable Member

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.

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors