How to get the the sum of previous month values in DAX

[Anonymous]

Hi All,

Can you please help me to achieve a logic to calculate the Final Value(column) logic by using DAX .

My case is to have "0' for January 2022 and for Feb the value should be Jan 2022 and For April the value should be sum(Jan,feb,Mar) and for May it should be sum(Jan,Feb,mar,Apr).

I am creating a measure , For which  I have created "start range" and "End Range" measures to get this Final Value column to be created. Please help

Month	Start Range	EndRange	Attrition	Final Value
1/1/22	1/1/22	1/31/22	10	0
2/1/22	1/1/22	1/31/22	20	10
3/1/22	1/1/22	2/28/22	30	30
4/1/22	1/1/22	3/31/22	40	60
5/1/22	1/1/22	4/30/22	50	100
6/1/22	1/1/22	5/31/22	60	150
7/1/22	1/1/22	6/30/22	70	200

Thanks in advance,

[OliT]

Hi @Anonymous 

Please try this measure

measure =
SUMX (
    FILTER ( ALL ( 'table' ), 'table'[MonthStart] < MAX ( 'table'[MonthStart] ) ),
    [Attrition]
)

Regards,

OliT

[Anonymous · Accepted solution]

Hello, 
You can use the following measure to calculate rolling sum for previous months. 

Find Value =

IF(SELECTEDVALUE('Table'[Month]) = 1, 0,

CALCULATE(

SUM('Table'[Attrition]),

FILTER(

ALLSELECTED('Table'[Date]),

'Table'[Date]< MAX('Table'[Date]))

))

Output:

Month = Month(Table[Date])

[Anonymous]

Hi @Anonymous 

Thanks for the quick reply,

Unfortunately I cannot use "Attrition" in the SUM because  it was derived using the below logic measures. Please help me how to crack this.

Attrition = CALCULATE (

DISTINCTCOUNT( employee[employee_code] ),

  employee[Attrition filter] = "yes"

)

Attrition filter =

var monthkey = year([Month])&FORMAT(month([Month]),"00")

var lwdmmyy= year([LWD])&FORMAT(month([LWD]),"00")

return

IF( lwdmmyy=monthkey, "yes","No")

Thanks,

[Anonymous]

@ddpl - Hi, Looking for your reply/ any alternate solution. Thank you in advance

[ddpl]

@Anonymous ,Hey bro

You don't need other column to get running total like Start or End Range

Just create a measure as below and put it along with Date and Attrition in Table...

Your Need = 

   VAR need = CALCULATE(SUM('Table'[Attrition]), FILTER(ALLSELECTED('Table'), 'Table'[Date]<MAX('Table'[Date])))

   RETURN IF(need=BLANK(), 0, need)

[Anonymous]

Hi @ddpl ,

Thanks for the quick reply,

Unfortunately I cannot use "Attrition" in the SUM because  it was derived using the below logic measures. Please help me how to crack this.

Attrition = CALCULATE (

DISTINCTCOUNT( employee[employee_code] ),

  employee[Attrition filter] = "yes"

)

Attrition filter =

var monthkey = year([Month])&FORMAT(month([Month]),"00")

var lwdmmyy= year([LWD])&FORMAT(month([LWD]),"00")

return

IF( lwdmmyy=monthkey, "yes","No")

Also, what is  need in the below line of your code:

RETURN IF(need=BLANK(), 0, need)

Thanks

[amitchandak]

@Anonymous , You can join date with date table and can use time intelligence

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA