Running Total that Resets at Start of Month

[Anonymous]

Hello,

I'm attempting to create a running total for an accounting dataset in a similar manner to this post:

Running Total with Reset 

The main difference to that post is that I need the running total of NetChange to be calculated for each customer on a per day basis which then resets at the start of a new month.  Here is an example of what the table looks like:

Most of the days have no charges associated with them but are necessary to keep.  This example is for one customer but the actual table contains over a thousand customers that individually need the running total calculated.

Any idea how to approach this problem?

[Whitewater100]

Hi:

Can you try the MTD pattern?

Total Sales RT Month =

CALCULATE(

    Sum(YourTableName[Net Change]),

    DATESMTD(Dates[Date]

))

You need a basic date table,marked as a date table and connected to this fact table (one to Many) Date to Fact.

Here is DAX date table code you can use and modify under the new table area.

Dates =

-- Specify a start date and end date

VAR StartDate = Date(2021,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

I hope this helps!