Calculate Sum of previous month and add to current month for non-calandar dates

[lem5072]

I want to show hires for our office each month, and then to have the previous months hire total added to the next month to get a cummulative by month count of the hires. I need this to run from an Oct 1st start to a Sept 30th end (already created a date table for this)

For example in Oct we hired 30 people, then in Nov we hired 25 people the dot (or line) for Nov would be at 55, for Dec we hired 10 people so the total for Dec would be 65, etc to January. 

Right now I have my code as 

Sum MoM EOD =

Calculate(

COUNT('EOD Report'[EOD Date]),

Datesytd(Dates[Date])

)

With the Dates being the Date Table.

[Nathaniel_C]

Hi @lem5072 
Try this if I understood your question, the filter restricts the dates to start at October, and go through cur date. That is why I included Sept to illustrate where it starts.

Running Total = 

var _today = TODAY()
var _startingDate = IF(MONTH(_today)>9, DATE(YEAR(_today),10,1),DATE(YEAR(_today)-1,10,1))


var _curDate = MAX(TableMine[Date])
var _sumHired = SUM(TableMine[Hired])

var _calc = CALCULATE([Sum of Hired],FILTER(ALL(TableMine),TableMine[Date]>=_startingDate && TableMine[Date]<=_curDate)

)

return _calc

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[lem5072]

Hi @Nathaniel_C 

I am still getting a return of the months total count not the cummulative sum. If I am using the _sumHired as a count function of persons dates do you think that needs to be changed to a sum function of something else to work? 

Month Sum Count =

var _today = today()

var _StartingDate = if(month(_today)>9, date(year(_today),10,1),Date(year(_today)-1,10,1))

var _curDate = max('EOD Report'[EOD Date])

var _sumHired = count('EOD Report'[EOD Date])

var _calc = calculate(_sumHired,filter('EOD Report','EOD Report'[EOD Date]>=_StartingDate && 'EOD Report'[EOD Date]<=_curDate)

)

return _calc

[Nathaniel_C]

Hi @lem5072 ,
Sorry, I did not include that

Sum of Hired = SUM(TableMine[Hired])

Sometimes for a quick return, you know.😀
When you post a picture or use words, I create a table instead of actual data.  Better to provide a pbix.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[lem5072]

HI @Nathaniel_C

Thanks for being patient with me. That correction still didn't result in a cummulative Month over month. I cannot attach the PBIX due to other PII information but have attached the snippet of the data which I am using in Excel in hopes this can help 

https://docs.google.com/spreadsheets/d/18pquWJFGEz14BIEKBDiw_hS4QsvPWhC48zj0-Aqv-gQ/edit?usp=sharing

[Nathaniel_C]

Hi @lem5072 ,
Your question "My issue now is how would i not show any data for months that have not occurred yet? " This will be solved by adding the IF() at the RETURN line.  I also added comments that should make it easier to follow along.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel 

Running Total =
VAR _today =  //get today'date
    TODAY ()
VAR _startingDate = //make the date that you wish to start
    IF (
        MONTH ( _today ) > 9,
        DATE ( YEAR ( _today ), 10, 1 ),
        DATE ( YEAR ( _today ) - 1, 10, 1 )
    )
VAR _curDate = //get the date of the current row
    MAX ( TableMine[Date] )

VAR _calc = //Do the running total
    CALCULATE (
        [Sum of Hired],
        FILTER (
            ALL ( TableMine ),
            TableMine[Date] >= _startingDate
                && TableMine[Date] <= _curDate
        )
    )
RETURN //if today is greater than current date, return the _calc otherwise make it blank. Any days previous to the starting date are alreadynot included
    IF ( _today > _curDate, _calc, BLANK () )

[Nepal101]

MoM count value = CALCULATE(

DISTINCTCOUNT(Sheet1[workerName ]),

DATESINPERIOD('Date table'[Date],Max('Date table'[Date]), -12 , Month))
you can see in the picture for the month of August there are 5 counts in the month of sep it is the same count as well because there wasn't any new hire but on October it changed to 10 so on and so forth.

[lem5072]

hi @Nepal101 

I am still not getting a cummulative count for each month 

MoM count value = CALCULATE(

SUM('EOD Report'[Index]),

DATESINPERIOD(Dates[Date],Max(Dates[Date]), -12 , Month))

[Nepal101]

Hello @lem5072 
Instead of using the sum can you use the distinct count in your formula. 

Thank you 

[lem5072]

When I use DistinctCount it returns a value of 1 for each month (because I index the count of persons (since people have historically shared names and there is not person centric unique identifier in the dataset)

[Nepal101]

I tried using the Index and if you create a measure using the index column you can achieve the value. Please try.  Creating the first measure using the index column and then using the same measure in creating the second measure. 

[Nepal101]

Can you count the hire date that will give you the count in total and use that in the formula? 

First measure :-count the hire date = count(Sheet1[hired date ])
Second measure:- Measure 1 = CALCULATE(

[count the hire date],

DATESINPERIOD('Date table'[Date],Max('Date table'[Date]), -12 , Month))
Hopefully this helps. 

[lem5072 · Accepted solution]

Hi @Nepal101

Thanks for being patient with me. That correction still didn't result in a cummulative Month over month. I cannot attach the PBIX due to other PII information but have attached the snippet of the data which I am using in Excel in hopes this can help 

https://docs.google.com/spreadsheets/d/18pquWJFGEz14BIEKBDiw_hS4QsvPWhC48zj0-Aqv-gQ/edit?usp=sharing 

[lem5072 · Accepted solution]

Thanks for being patient with me. That correction still didn't result in a cummulative Month over month. I cannot attach the PBIX due to other PII information but have attached the snippet of the data which I am using in Excel in hopes this can help