Measure to apply a business rule from one month to another

[Niams93]

Hello ! 

I'm here to seek help with a measure that I've been struggling with for a while and would be really grateful if someone has an idea about the issue. Here is the case : 

I've got 3 tables :

- a Dimension table 'Table - Products' 

- a Fact Table 'Table - Provision' 

- a Calendar Table 'Table - Calendar'

Relationships : 

'Table - Products' is related to 'Table - Provision' thanks to the fields 'Table - Products'[ID Product] and 'Table - Provision'[ID Product]. 

'Table - Provision' is linked to 'Table - Calendar' via the fields 'Table - Provision'[ID CalendarDate] and 'Table - Calendar'[Date Int]. 

I'm pretty sure that the relationships are well defined, as this dataset was already functionnal without the measure I'm trying to add and it doesn't use any new source.  

Business case : 

I would like to create a measure that will identify the products 'Table - Provision'[Product Number] that are in the company's stock in the month that the user will select in their report, but that were not in the company's stock the in the previous month of the one selected before. These Products will be the "New Products" as they are new. The calendar table contains fields like 'Table - Calendar'[Month Number], 'Table - Calendar'[Month Name] or Table - Calendar[Current Month] that could be used here. 

The measure will then identify the products 'Table - Provision'[Product Number] that are not the in company's stock in the month that the user will select in their report, but that were here in the company's stock the in the previous month of the one selected before. These Products will be the "Obsolete Products" as they are not here anymore.

Using these numbers, the measure must apply the following calculation : 

("New Products" + "Obsolete Products"/2) / the total number of 'Table - Provision'[Product Number] in the previous month of the one selected by the user.  

The users will use this measure for instance in a table visual, with 3 columns : one with the years, one with the months and another one with the measure that will display for each month the result of the calculation described above. 

The technical constraints : 

- the calculation must be done in a measure 

-Power Query can't be used 

-No calculated columns can be created 

 What I've tried so far, as I don't have a lot of experience in DAX and had to use a little bit of AI :

VAR SelectedMonth = SELECTEDVALUE('STANDARD - Calendar'[Month Number]) --Getting the month selected by the user

VAR PrevioussMonth = IF (SelectedMonth = 1,12, SelectedMonth - 1) --I know...a not very clean "trick" to get the month number of December if January is selected 

VAR CurrentMonthProducts = --Getting the list of 'Table - Products'[Product Number] of the month selected

    CALCULATETABLE(

        VALUES('Table - Provision'[Product Number]),

        FILTER(

            'Table - Products',

            LEFT('Table - Products'[Brand], 3) = "XXX"

        ),

        FILTER(

            'Table - Provision',

            RELATED('Table - Calendar'[Current Month]) = SelectedMonth

        )

    )

VAR PreviousMonthProducts = --Getting the list of 'Table - Products'[Product Number] of the previous month of the month selected

    CALCULATETABLE(

        VALUES('Table - Provision'[Product Number]),

        FILTER(

            'Table - Products',

            LEFT('Table - Products'[Brand], 3) = "XXX"

        ),

        FILTER(

            'Table - Provision',

            RELATED('Table - Calendar'[Current Month]) = PrevioussMonth

        )

    )

VAR NewProductsCount = COUNTROWS(EXCEPT(CurrentMonthEmployees, PreviousMonthEmployees)) -- Getting the number of "New Products"

VAR ObsoleteProductsCount = COUNTROWS(EXCEPT(PreviousMonthEmployees, CurrentMonthEmployees)) -- Getting the number of "Obsolete Products"

VAR PreviousMonthCount = COUNTROWS(PreviousMonthEmployees) -- Getting the total number of Products in the previous month of the one selected 

RETURN

IF(

    PreviousMonthCount = 0,

    BLANK(), 

    (NewProductsCount + ObsoleteProductsCount / 2) / PreviousMonthCount

I've tried to break the measure down and test each time 2 visuals,  one with the measure alone and one with the measure + the month number : 

-TEST1 : COUNTROWS(CurrentMonthProducts)

Result -> got a number when used in the measure alone, and a blank when used with the [Month Number]

-TEST2 : COUNTROWS(PreviousMonthProducts)

Result -> got blank in both visuals, the problem is problably here  

-TEST3 : Return NewProductsCount

 Result -> Same as TEST1

-TEST4 : Return ObsoleteProductsCount

Result -> Same AS TEST2

I hope I was clear in my explanations, feel free to ask for details if needed, thank you in advance ! 

Note : the tables and variables names were changed, don't be surprised if the calculation or business rules don't make sense 🙂

[v-tsaipranay]

Hi,

I wanted to check if you had the opportunity to review the information provided. Also have you checked information provided by @johnt75 , @Poojara_D12 , thank you for your insights. Please feel free to contact us if you have any further questions. 

Thank you.

[FBergamaschi]

I see you already got answers, if they are not satisfactory, please include a small rows subset (not an image) of all the tables involved in your request, so that we can import them in Power BI and reproduce the data model.  In this way we can reproduce the problem and help you. Thank you

[Poojara_D12]

Hi @Niams93 

You're calculating a churn-like metric in Power BI that measures the turnover of products month-over-month by comparing which products are new or obsolete between two consecutive months. Specifically, you're using DAX to count how many products appear in the current month but not the previous (new products), and how many appeared in the previous month but not the current (obsolete products). Your formula then takes the sum of new and half the obsolete products, divided by the total number of products from the previous month.

The challenge arises in how DAX handles time-based filtering and relationship context. In your current implementation, you filter the data using month numbers or related calendar values, but this can lead to unreliable results—especially if the same month number appears across multiple years or if the context transition in the DAX query isn’t properly maintained. Additionally, using RELATED inside CALCULATETABLE for time filtering might not behave as expected due to how row and filter contexts operate in DAX.

To fix this, you need to explicitly define the date range for both the current and previous months using DATE, EOMONTH, or other reliable date functions. By calculating the first and last day of each month and using them to filter the calendar or fact table directly, you ensure that only records from the intended periods are evaluated. The key improvement is shifting from a potentially ambiguous filter based on month numbers to a precise date-based filter logic that works consistently across the model. This way, your EXCEPT logic comparing sets of product numbers works reliably, and your churn formula produces accurate results regardless of the time period selected in visuals or slicers.

[v-tsaipranay]

Hi @Niams93 ,

Thank you for reaching out to the Microsoft Fabric Community Forum and detailed explanation and appreciate @johnt75  for sharing a possible approach.

The DAX provided correctly handles your requirement using DATEADD to shift context by one month and uses EXCEPT to compare product sets. This ensures the measure dynamically calculates:

(New Products + Obsolete Products ÷ 2) ÷ Products in Previous Month

If you also need to limit the results to specific brands (e.g., brand code starting with "XXX"), you can apply that condition using a CALCULATETABLE filter or manage it directly via a slicer on the report. The logic should work well in visuals with Year and Month context.

I hope this reslove your issue, if you need any further assistance, feel free to reach out.

Thank you.

[Niams93]

Hi, thank you for your help, I've added the filter in a CALCULATETABLE and that worked fine 🙂 

[johnt75]

You can try

My Measure =
VAR CurrentMonthProducts =
    VALUES ( 'Table - Provision'[Product Number] )
VAR PrevMonthProducts =
    CALCULATETABLE (
        VALUES ( 'Table - Provision'[Product Number] ),
        DATEADD ( 'Table - Calendar'[Date], -1, MONTH )
    )
VAR NewProducts =
    EXCEPT ( CurrentMonthProducts, PrevMonthProducts )
VAR ObsoleteProducts =
    EXCEPT ( PrevMonthProducts, CurrentMonthProducts )
VAR Result =
    DIVIDE (
        COUNTROWS ( NewProducts ) + DIVIDE ( COUNTROWS ( ObsoleteProducts ), 2 ),
        COUNTROWS ( PrevMonthProducts )
    )
RETURN
    Result

[Niams93]

Hello @johnt75, 

Thank you for your answer. I've tried the solution but unfortunately have the an issue when used, here's what I tried : 

- When I return Result and use the measure alone in a table or with another column 'Table - Calendar'[Month Number], both tables stay empty 

-When I return COUNTROWS(CurrentMonthProducts) :

    - if I use the measure alone in a table, I've got the total of all my Products rows of my Dimension Table

    -  if I use the measure in a table with another column 'Table - Calendar'[Month Number], I've got the           same total of rows as above but in each line of the table. 

-When I return COUNTROWS(PrevMonthProducts), I've got the same results as COUNTROWS(CurrentMonthProducts). 

I have also just noticed that I made a mistake when "anonymizing" my measure in my first post : the field [Product Number] does not belong to the Fact 'Table - Provision' but to the dimension table Table - Products', I'm sorry for the mistake. 

[johnt75 · Accepted solution]

The fact that the product number is in the dimension table rather than the fact table means you need to change the code a bit.

My Measure =
VAR CurrentMonthProducts =
    SUMMARIZE ( 'Table - Provision', 'Table - Products'[Product Number] )
VAR PrevMonthProducts =
    CALCULATETABLE (
        SUMMARIZE ( 'Table - Provision', 'Table - Products'[Product Number] ),
        DATEADD ( 'Table - Calendar'[Date], -1, MONTH )
    )
VAR NewProducts =
    EXCEPT ( CurrentMonthProducts, PrevMonthProducts )
VAR ObsoleteProducts =
    EXCEPT ( PrevMonthProducts, CurrentMonthProducts )
VAR Result =
    DIVIDE (
        COUNTROWS ( NewProducts ) + DIVIDE ( COUNTROWS ( ObsoleteProducts ), 2 ),
        COUNTROWS ( PrevMonthProducts )
    )
RETURN
    Result

Unless you only have 1 year of data the field month number probably won't work well - the data for the given month number will include numbers from all years. You probably want to use a combination of year and month.

[Niams93]

Perfect, that worked even with the filter I added, thank you so much !
Indeed I needed to use my field "Year-Month" from my calendar table to display the correct result. 
Just one last question if I may : if I wanted to change a little bit this measure to compare the month selected to the January month from the current year (instead of the previous month of the one selected), what kind of change could I add ?  
What I tried :

I changed : 

VAR PrevMonthProducts =
    CALCULATETABLE (
        SUMMARIZE ( 'Table - Provision', 'Table - Products'[Product Number] ),
        DATEADD ( 'Table - Calendar'[Date], -1, MONTH )
    )

 
To : 

VAR PrevMonthProducts =
    CALCULATETABLE (
        SUMMARIZE ( 'Table - Provision', 'Table - Products'[Product Number] ),
        MONTH('STANDARD - Calendar'[Date]) = 1,
        YEAR('STANDARD - Calendar'[Date]) = YEAR(TODAY())
    )

 But I think I'm doing something wrong since the result seems off, is there a better way ? 

[johnt75]

Normally there would only be 1 calendar table in a model, and it would be marked as a date table, but perhaps the different names only reflect changes you've made to table names for the purposes of posing the question here.

When you are manipulating fields on the calendar table which are not the actual date column you need to manually add a REMOVEFILTERS, which is done automatically when manipulating the date column - one of the advantages of marking it as a date table. You could change your code to

VAR PrevMonthProducts =
    CALCULATETABLE (
        SUMMARIZE ( 'Table - Provision', 'Table - Products'[Product Number] ),
        MONTH('STANDARD - Calendar'[Date]) = 1,
        YEAR('STANDARD - Calendar'[Date]) = YEAR(TODAY()),
		REMOVEFILTERS('STANDARD - Calendar' )
    )