Forum Discussion
Add new column with filters based on current row values
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.
Super UserAnonymous , 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-64b0b4a4090e
you 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#M184
Appreciate 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.