Skip to main content
cancel
Showing results for 
Search instead 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

Reply
samulik
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

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

14 REPLIES 14
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

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:

 

samulik_0-1644381954405.png

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Of course, here are the download links:

 

The PBI-file

 

The sample excel data

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.  

You are welcome


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
samulik
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?

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.

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.

 

samulik_1-1644382434859.png

 

Can the ALLSELECTED be tweaked slightly to make this work?

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

 

samulik_0-1643621417625.png

@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))
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
Super User
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.

 

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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