Trying to calculate changes from previous time periods

[samulik]

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

[Ashish_Mathur · Accepted solution]

Share the download link of the PBI file.

[Ashish_Mathur]

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)

[samulik]

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. 

[Ashish_Mathur · Accepted solution]

Share the download link of the PBI file.

[samulik]

Of course, here are the download links:

The PBI-file

The sample excel data

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[samulik]

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.  

[Ashish_Mathur]

You are welcome

[samulik]

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?

[v-shex-msft]

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

[samulik]

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?

[samulik]

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.

[amitchandak]

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

[samulik]

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.

[amitchandak]

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