I'm struggling to get the calendar functions to work as intended. I have built an example inventory table with just three columns: date, product, inventory. The inventory is the amount of the product in stock at the given date. I have created a Calendar table with CALENDAR to contain enough dates. The inventory[date] has a relationship with the Calendar table. The relationship is one-to-many as the inventory table has multiples of the dates (for each product).
I'm calculating the inventory for the previous month with
Inventory PM = CALCULATE(MIN(inventory[Inventory]), PREVIOUSMONTH(inventory[Date]))
Monthly change, % = DIVIDE([Inventory] - [Inventory PM], [Inventory PM])
Solved! Go to Solution.
Share the download link of the PBI file.
Inventory PM should be a measure (not a calculated column). The input to the previousmonth() function should be calendar[date]
Inventory PM = CALCULATE(MIN(inventory[Inventory]), PREVIOUSMONTH(Calendar[Date]))
Monthly change % should also be a measure (not a calculated column)
Thank you. But, alas, I'm still not seeing the numbers. I do have the Inventory PM as a measure. I also tried with the Calendar table date, but still it gives just blank, below the measure is viewed in a table visualization:
Inventory[Date] is linked to Calendar[Date].
I didn't get to the monthly change yet as the previous month numbers are not working yet.
Share the download link of the PBI file.
Thank you! I marked this as Solution, as the PBI file worked. It took a while for me to figure out the original mistake: the "Inventory PM" measure was correct, but in the table visualization I had selected the Inventory Date as a field instead of the Calendar table Date. I find it a bit annoying that it didn't work as the relationship was there. People did say that I need to refer to the calendar table date, but I just checked the DAX code, not the visualization filters.
Then changing the monthly change to measure instead of a calculated column gave the right answer.
I tried to generate a Quick Measure for this, with "Time Intelligence: Month-over-Month change". I was able to generate the following DAX:
Sum of Inventory MoM% =
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_MONTH =
DATEADD('Date'[Date].[Date], -1, MONTH)
DIVIDE(AVERAGE('Inventory'[Inventory]) - __PREV_MONTH, __PREV_MONTH)
...but this also give just zeroes. What might be wrong with my Date settings or relations?
I'd like to suggest you use the date function to manually calculate the filtered date range instead of time intelligence functions. It is more simple to use and not required the calendar table.
Sum of Inventory MoM% = VAR currDate = MAX ( 'Inventory'[Date] ) VAR __PREV_MONTH = CALCULATE ( SUM ( 'Inventory'[Inventory] ), FILTER ( ALLSELECTED ( 'Inventory' ), [Date] = DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) ) ) ) RETURN DIVIDE ( SUM ( 'Inventory'[Inventory] ) - __PREV_MONTH, __PREV_MONTH )
Thank you very much! I feel that this is very close to a working solution. I didn't try this kind of a date calculation in the first place as the official DAX documentation (https://docs.microsoft.com/en-us/dax/date-function-dax) mentions that "month: Valid values are 1-12". But I see that using month = 0 gives the december from the previous year, so this works despite what the docs say. (I understand that the "over the year" -calculations can be done manually, but it is replicating the time intelligence functions and gets complicated quite quickly.)
But there is a problem that the SUM calculates the inventory for all products from the previous month. The screenshot shows that the Inventory PM is much higher than that of the current product. The test table also has a product "Coconut" and the sum calculates all of the products.
Can the ALLSELECTED be tweaked slightly to make this work?
Thank you for the suggestion. I tried to change to reference the Date (Calendar) table, but what happened was that the "Inventory PM" comes up as blank. My relationship is set up as in the screenshot below.
@samulik , either date - Inventory date has timestamp or date table do not have all required dates
Can you share a sample pbix after removing sensitive data.
Thank you again for the suggestions, I appreciate them!
The Inventory[Date] has "Data type: Date" and the calendar table "Date" is created with
Date = CALENDAR(DATE(1980, 01, 01), DATE(2025, 12, 31))
@samulik , Please use date table for time intelligence, always use date table marked as date table
Inventory PM = CALCULATE(MIN(inventory[Inventory]), PREVIOUSMONTH(Date[Date]))
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
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 :radacad sqlbi My Video Series Appreciate your Kudos.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.