Forum Discussion
Create table with missing values compared to previous month
Hello,
I have a table with a project ID, Date and Status. Every month, I add new data under the existing one and I would like to have a table the always compare the last available month in the table with the previous month and add the project id that disappeared in the current month, together with their last status (from the previous month).
Also, all my data is on the same table, so I will not compare the content of two tables, but only the content in one table that belongs to this month and the previous month.
Reading the forum I understand that I might be able to achieve this by using the EXCEPT function but I cannot get it to work.
Thank you!
LostintheBIu - Would need sample data to be specific, but in theory:
Missing Projects Table = VAR __CurrentMonth = MAX([Date]) VAR __PreviousMonth = EOMONTH(__CurrentMonth,-1) VAR __CurrentTable = SELECTCOLUMNS( FILTER( ALL('Table'), YEAR([Date])=YEAR(__CurrentMonth) && MONTH([Date])=MONTH(__CurrentMonth) ), "Project ID",[ProjectID] ) VAR __PreviousTable = SELECTCOLUMNS( FILTER( ALL('Table'), YEAR([Date])=YEAR(__PreviousMonth) && MONTH([Date])=MONTH(__PreviousMonth) ), "Project ID",[ProjectID] ) RETURN EXCEPT(__PreviousTable,__CurrentTable)
5 Replies
LostintheBIu - Would need sample data to be specific, but in theory:
Missing Projects Table = VAR __CurrentMonth = MAX([Date]) VAR __PreviousMonth = EOMONTH(__CurrentMonth,-1) VAR __CurrentTable = SELECTCOLUMNS( FILTER( ALL('Table'), YEAR([Date])=YEAR(__CurrentMonth) && MONTH([Date])=MONTH(__CurrentMonth) ), "Project ID",[ProjectID] ) VAR __PreviousTable = SELECTCOLUMNS( FILTER( ALL('Table'), YEAR([Date])=YEAR(__PreviousMonth) && MONTH([Date])=MONTH(__PreviousMonth) ), "Project ID",[ProjectID] ) RETURN EXCEPT(__PreviousTable,__CurrentTable)Hi Greg!
After adjusting some variables, to solution worked perfectly. Just one additional small thing that I was asking initially. If I also have a column called "budget", let's say, how can I copy that information from the last month in the newly created table, next to project ID?
Where should the code be included?
Thank you!
LostintheBIu - You should just be able to add a calculated column to that table and maybe use something like LOOKUPVALUE or MAXX(FILTER(...)...) ?
LostintheBIu , Looking at this you can use Time Intelligence.
example. Mofidy as per purpose
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])If not resolved - Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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.
