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)
amitchandak's avatar
amitchandak
Super User
5 years ago

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.