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 - 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's avatar
    LostintheBIu
    Helper II
    5 years ago

    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'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?