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.
Good afternoon everyone,
I'm trying to create a measure that brings in all the rows in a column based on a filter.
If this was a column, and I'd like to keep only the LeadId where the created date was last month, I'd write something like:
if(month(leadid)=month(today())-1,LeadId, ""). But how to do that in a measure that would display all the Lead IDs that fall under that expression?
I tried combining FILTER and VALUES, but VALUES returns nothing.
Thanks!
Solved! Go to Solution.
Hi @Alex-PBIComm ,
You could use DATEADD function to realize it:
First create a dim calendar table;
Then create a measure as below:
Measure = IF(NOT(ISFILTERED('calendar table'[Month])),MAX('Table'[date]),CALCULATE(MAX('Table'[date]),DATEADD('calendar table'[Date],-1,MONTH)))
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
@Alex-PBIComm , if you have date you can use time intelligence
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)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Thanks for the reply, but I'm not looking for a calculation, I want the measure to return the list of items in a column that reflect the filtering.
Hi @Alex-PBIComm ,
You could use DATEADD function to realize it:
First create a dim calendar table;
Then create a measure as below:
Measure = IF(NOT(ISFILTERED('calendar table'[Month])),MAX('Table'[date]),CALCULATE(MAX('Table'[date]),DATEADD('calendar table'[Date],-1,MONTH)))
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
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 |
---|---|
106 | |
105 | |
80 | |
68 | |
61 |
User | Count |
---|---|
143 | |
104 | |
103 | |
82 | |
70 |