Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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 |
Solved! Go to 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"))
Proud to be a 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"))
Proud to be a 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"))
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.