Add Previous Day to a Set Filter

[Syndicate_Admin]

Hi, I need to make a measurement to calculate the growth or decrease of customers between dates.

On the one hand, I have a table with the daily grouped value of customers by date and by salesperson. This table has 2 relationships, to a calendar table and to the commercial table.

I need to calculate the growth between dates with a Slicer, where I filter the dates and give myself the growth between those dates. The formula is simple, (end customer - initial customers), the problem comes when the initial customers must be those from the day before the initial date of the filter, since I already have the formula for the growth between the two dates of the filter. I'm going to try to explain it with data:

- I want to apply a filter that marks my growth from 01/04/2024 to 30/04/2024

- The data I have is as follows:

The BI with the formulas used is giving me the growth of the last column, which is erroneous because it is omitting variation that goes from the last day of the previous one to the first month of the filter. The data I am looking for is to be able to take the growth from the "Monthly Growth" column, which is taking into account the variation of that first day. Logically, this is what I want to take to Daily growth as well.

The table has the following names, in case it's easier for you, where CUPs Net would be the variable for the data I'm looking for.

I hope I have explained myself well, thank you very much.

[K0da]

Hi,

From what i understand you need the growth to not be affected by the initial date on the slicer, i couldn't figure out if it was a monthly growth or daily growth so i made four columns:

Month_Year = MONTH('Dataset'[Date]) & "/" & YEAR('Dataset'[Date])

Monthly_Growth_Within_Month = 
VAR Client_Last_Day = CALCULATE(
    SUM('Dataset'[N of clientes]),
    FILTER(
    ALL('Dataset'),
        EARLIER('Dataset'[Month_Year]) = 'Dataset'[Month_Year] && ENDOFMONTH('Dataset'[Date]) = 'Dataset'[Date]
    )
)

VAR Client_First_Day = CALCULATE(
    SUM('Dataset'[N of clientes]),
    FILTER(
    ALL('Dataset'),
        EARLIER('Dataset'[Month_Year]) = 'Dataset'[Month_Year] && STARTOFMONTH('Dataset'[Date]) = 'Dataset'[Date]
    )
)

RETURN Client_Last_Day-Client_First_Day

Monthly_Growth_Between_Months = 

VAR LAST_DAY_PREVIOUS_MONTH = LASTDATE(PREVIOUSMONTH('Dataset'[Date]))

VAR Client_Last_Day_Previous_month = CALCULATE(
    SUM('Dataset'[N of clientes]),
    FILTER(
    ALL('Dataset'),
        LAST_DAY_PREVIOUS_MONTH = 'Dataset'[Date]
    )
)

VAR Client_Last_Day_Current_Month = CALCULATE(
    SUM('Dataset'[N of clientes]),
    FILTER(
    ALL('Dataset'),
        EARLIER('Dataset'[Month_Year]) = 'Dataset'[Month_Year] && ENDOFMONTH('Dataset'[Date]) = 'Dataset'[Date]
    )
)

return Client_Last_Day_Current_Month-Client_Last_Day_Previous_month

Daily_Growth = 

var Current_Date = 'Dataset'[Date]
var Previous_Date = MAXX(FILTER(ALL('Dataset'[Date]), 'Dataset'[Date] < Current_Date), 'Dataset'[Date])
var Previous_Value = MAXX(FILTER('Dataset', 'Dataset'[Date] = Previous_Date), 'Dataset'[N of clientes])

return 'Dataset'[N of clientes]-Previous_Value

The result table

[Syndicate_Admin]

Hello, thank you very much for the answer. The goal is to get to your Daily_Growth column so that the growth filter will work for any date of the slicer. The problem is that I think it should be a measure and not a column, so that all segmentations also work. My data table has the following slicers and I need to get the variations in Net CUPs for each of the fields in the table:

• Date
• Energy Provider
• Delegation
• Collaborator
• Commercial

Thank you very much for your input.

Best regards