Forum Discussion
Syndicate_Admin
2 years agoAdministrator
Add Previous Day to a Set Filter
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 salesper...
K0da
2 years agoNew Member
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_Admin2 years agoAdministrator
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