cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Trying to calculate changes from previous time periods

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]))`
Then I have a calculated column for the monthly change set up with
`Monthly change, % = DIVIDE([Inventory] - [Inventory PM], [Inventory PM])`
The Inventory PM gives data, but the monthly change comes up as blank. It works if I have a table with just one product, i.e. no multiples of the dates.

What am I missing here? I obviously don't understand the Power BI way of dealing with calendar related stuff yet...

I apologize if this has been answered elsewhere frequently, I also did find a lot of answers to this "monthly change" question and I have built my solution based on those, but I'm still not getting it to work...
2 ACCEPTED SOLUTIONS
Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
14 REPLIES 14
Super User

Hi,

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)

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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:

I didn't get to the monthly change yet as the previous month numbers are not working yet.

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

The PBI-file

The sample excel data

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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.

Super User

You are welcome

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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% = IF(    ISFILTERED('Date'[Date]),    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 =        CALCULATE(            SUM('Inventory'[Inventory]),            DATEADD('Date'[Date].[Date], -1, MONTH)        )    RETURN        DIVIDE(AVERAGE('Inventory'[Inventory]) - __PREV_MONTH, __PREV_MONTH))`

...but this also give just zeroes. What might be wrong with my Date settings or relations?

Community Support

HI @samulik,

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.

Time Intelligence "The Hard Way" (TITHW)

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

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

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?

Frequent Visitor

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.

Super User

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

Frequent Visitor

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))`
I was testing "history data", hence quite a large date spectrum. Doesn't work with a more narrow date range either.

Is it allowed to post an example .pbix on these forums, if I have removed all the other data than the data needed to solve the example dilemma.

Super User

@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
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
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.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors