Forum Discussion

LostintheBIu's avatar
LostintheBIu
Helper II
5 years ago
Solved

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 ta...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    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)
Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

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

LostintheBIu's avatar
LostintheBIu
Helper II
5 years ago

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?