Dax: Sum of current month to last month values

[Anonymous]

Hello All,

I need to calculate the sum of values as,

For above table,

For Month-Year Apr-2016----> _Sum OF headcount = Apr-2016 value(there's no previous month values),

Month-Year May-2016----> _Sum OF headcount = Apr-2016 _HeadCount value +May-2016 _HeadCount Value,

Month-Year Jun-2016----> _Sum OF headcount = Apr-2016 _HeadCount value +May-2016 _HeadCount Value +Jun-2016 _HeadCount Value,

Like wise i need to calculate the sum of headcount values for that perticular month to previous month values for every month.

Any help Please.

Mohan V

[Anonymous]

How about something like:

SumHeadcount = VAR CurrentMonth LASTDATE('YourTable'[MonthYear])
VAR LastMonthNum = if(Month(CurrentMonth) = 1, 12, MONTH(CurrentMonth) - 1)
VAR LastMonth = if(Month(CurrentMonth) = 1, DATE(YEAR(CurrentMonth]) - 1, LastMonthNum, 1), DATE(YEAR(CurrentMonth]), LastMonthNum, 1))
RETURN

CALCULATE(
	SUM('YourTable'[_Headcount]),
	ALL('YourTable')
	'YourTable'[Month Year] >= LastMonth,
	'YourTable'[Month Year] <= CurrentMonth
)

If your headcount is actually a measure, you can replace "SUM('YourTable'[_Headcount])" with your measure.

[Zubair_Muhammad]

Hi @Anonymous

Try this Calculated Column

=
CALCULATE (
    SUM ( TableName[HeadCount] ),
    FILTER (
        ALL ( TableName ),
        TableName[Month Year] <= EARLIER ( TableName[Month Year] )
    )
)

[Zubair_Muhammad]

@Anonymous

As a MEASURE you could try

Measure =
CALCULATE (
    SUM ( TableName[HeadCount] ),
    FILTER (
        ALL ( TableName ),
        TableName[Month Year] <= SELECTEDVALUE ( ( TableName[Month Year] ) )
    )
)

[Anonymous]

@Anonymous  @Zubair_Muhammad All your suggestions are worked like a charm

You guys are awsome.

Perfect.

But here i got an issue that,

when i included Gender in this table then it didnt given values as expected.

Here i would like to get the sum value as

for Gender Female type of 4/1/2016 = 2548,

Now this should get summed with 5/1/2016 Female value and should give 2548+2556=5104

Like wise it should get summed values for all the months

Is it possible.??

[Zubair_Muhammad]

Hi @Anonymous

Just replace ALL(TableName) with  ALLexcept( TableName ,TableName[Gender])

=
CALCULATE (
    SUM ( TableName[HeadCount] ),
    FILTER (
        ALLEXCEPT ( TableName, TableName[Gender] ),
        TableName[Month Year] <= EARLIER ( TableName[Month Year] )
    )
)

[Zubair_Muhammad · Accepted solution]

@Anonymous

Similarly you can adjust the MEASURE

Measure =
CALCULATE (
    SUM ( TableName[HeadCount] ),
    FILTER (
        ALLEXCEPT ( TableName, TableName[Gender] ),
        TableName[Month Year] <= SELECTEDVALUE ( ( TableName[Month Year] ) )
    )
)