Create table with missing values compared to previous month

[LostintheBIu]

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!

[Greg_Deckler · Accepted solution]

@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)

[LostintheBIu]

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!

[Greg_Deckler]

@LostintheBIu - You should just be able to add a calculated column to that table and maybe use something like LOOKUPVALUE or MAXX(FILTER(...)...) ?

[LostintheBIu]

Unfortunately I cannot use LOOKUPVALUE because having 3 or more months of data, a project ID might appear this (in the first two months) but not in the third month. This project ID will be in my table but the LOOKUPVALUE will return an error.

I was unable to make the MAXX(Filter()) work. Basically I want a vlookup that only takes into account rows where the Date equals today's month -1. How should this maxx and filter be built?

[amitchandak]

@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-Y...


Appreciate your Kudos.