Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi
I need to work out (using DAX ideally) how to calculate the monthly change in a caseload based on a cumulative total. A datasource I am using as part of a project im working on, from a third party, only shows the caseload each month, from this i need to work out the monthly changes over time. I know how i would do this in Excel but am unsure using DAX and havent been able to find a solution somewhere
I.e. Increase of 17 from Sept to Oct 2021
Any help would be greatly appreciated
Solved! Go to Solution.
Hi, @Anonymous
You can try calculated columns as below:
Previous_PIP =
CALCULATE (
MAX ( 'Table'[PIP] ),
FILTER (
'Table',
DATEADD ( 'Table'[Date], 1, MONTH ) = EARLIER ( 'Table'[Date] )
)
)
Result = IF(ISBLANK('Table'[Previous_PIP]),BLANK(),'Table'[PIP]-'Table'[Previous_PIP] )
Best Regards,
Community Support Team _ Eason
Hi, @Anonymous
You can try calculated columns as below:
Previous_PIP =
CALCULATE (
MAX ( 'Table'[PIP] ),
FILTER (
'Table',
DATEADD ( 'Table'[Date], 1, MONTH ) = EARLIER ( 'Table'[Date] )
)
)
Result = IF(ISBLANK('Table'[Previous_PIP]),BLANK(),'Table'[PIP]-'Table'[Previous_PIP] )
Best Regards,
Community Support Team _ Eason
DAX Date Table Code.
Dates =
-- Specify a start date and end date
VAR StartDate = Date(2020,1,1)
VAR EndDate = Today()
VAR FiscalMonthEnd = 6
-- Generate a base table of dates
VAR BaseTable = Calendar(StartDate, EndDate)
-- Add the Year for each individual date
VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))
-- Add the calendar month and other month related data for each date
VAR Months = ADDCOLUMNS(
Years,
"Month",MONTH([Date]),
"Year and Month Number",FORMAT([Date],"YYYY-MM"),
"Year and Month Name",FORMAT([Date],"YYYY-MMM"),
"Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),
"Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),
IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))
)
-- Add the Quarter and other quarter related data for each date
VAR Quarters = ADDCOLUMNS(
Months,
"Quarter",ROUNDUP(MONTH([Date])/3,0),
"Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))
-- Add the Day and other day related data for each date
VAR Days = ADDCOLUMNS(
Quarters,
"Day",DAY([Date]),
"Day Name",FORMAT([Date],"DDDD"),
"Day Of Week",WEEKDAY([Date]),
"Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)
-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date
VAR Weeks = ADDCOLUMNS(
Days,
"Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,
"Week of Year (Sunday)",WEEKNUM([Date],1),
"Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))
-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.
var WorkingDays = ADDCOLUMNS(
Weeks,
"Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})
RETURN WorkingDays
Hi:
You can add a Date Table and connect to Date. I'll paste DAX code for new Table below. After marking this as a Date Table, and connecting to your data table on Date to Date. The Date Table name is Date and the date column is called Dates. DATE[Dates] Month looks like DATES[Month] etc
Use the Month column or Month Year from Dates on your visual. I believe just a few measures can be used.
Amount = SUM(yourtablename[PIP])
User | Count |
---|---|
21 | |
14 | |
11 | |
8 | |
5 |
User | Count |
---|---|
24 | |
22 | |
20 | |
15 | |
10 |