Forum Discussion
Add new column with filters based on current row values
My main question here is around how to filter based on a value taken from the current column.
In this case I have a column Dates.Month (which is a whole number in the format YYYYMM) and a new calculated column Dates.MonthBefore (which is a whole number in the format YYYYMM, showing the code for the previous month). So in one row, is the Dates.Month is 202001, then Dates.MonthBefore is 201912. This bit works OK.
How do I create a new column that extracts the value from rows, looking for Dates.Month in the searched rows being equal to Dates.MonthBefore in the current row? The problem with Filter is it looks at hardcoded values only.
I want something like:
OpeningBalance = calculate(SUM(Table[Balance]), Table[Dates.Month] = Table[Dates.MonthBefore][thisrow])
I am sure there must be an easy answer to this!
Cheers
Hi Anonymous ,
Try this:
Column = VAR LastMonth = 'Table'[Dates.BeforeMonth] RETURN CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), 'Table'[Dates.Month] = LastMonth ) )or,
Column = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), 'Table'[Dates.Month] = EARLIER ( 'Table'[Dates.BeforeMonth] ) ) )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
Anonymous , in case you need a measure you can convert this into date
date = date(left([month year],4),right([month year],2),1)
Then you can use time intelligence
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])) 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])Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090eyou also use new column
sumx(filter(table,& eomonth([Date],0) = eomonth(arlier([Date]),-1)),[Amount])
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#M184Appreciate your Kudos.
also refer : https://youtu.be/yPQ9UV37LOU
This is probably the way to go long term, but Icey's solution is quicker for now. I will certainly revisit your suggestion at the next time calculation.
- Icey
Community Support
Hi Anonymous ,
Try this:
Column = VAR LastMonth = 'Table'[Dates.BeforeMonth] RETURN CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), 'Table'[Dates.Month] = LastMonth ) )or,
Column = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), 'Table'[Dates.Month] = EARLIER ( 'Table'[Dates.BeforeMonth] ) ) )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
