Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
Anonymous
Not applicable

Working out Monthly Increase from Dataset with only Cumulative Totals Using DAX

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

 

GWhitlock_0-1649685683591.png

 

Any help would be greatly appreciated

 

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

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] )

1.png

Best Regards,
Community Support Team _ Eason

View solution in original post

3 REPLIES 3
v-easonf-msft
Community Support
Community Support

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] )

1.png

Best Regards,
Community Support Team _ Eason

Whitewater100
Solution Sage
Solution Sage

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

Whitewater100
Solution Sage
Solution Sage

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])

 

Prev Month = CALCULATE([Amount], PREVIOUSMONTH(Dates[Date]))

Variance from Last Month = [Amount] - [Prev Month]
 
I hope this solves your question. Good luck!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.