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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.