How to write a dax to Get cumulative sum till 6th row and after 6th row rolling 6 months sum ?

[Anonymous]

HI Team,

I've requirement to calculate two different conditions:

1. To calculate cummulative sum till 6th row

2. To calculate Rollling 6 months sum (After 6 th row) 

Reference Link for PBI:     https://1drv.ms/u/s!Au-aOkl1BoHuhytgh_iqEh6UG4WA?e=IwUWDN

Formula:

Reference Link for PBI:     https://1drv.ms/u/s!Au-aOkl1BoHuhytgh_iqEh6UG4WA?e=IwUWDN

Please let me know if you need anything

Thanks In Advance

[Jihwan_Kim · Accepted solution]

Hi,

I suggest having a Dim-Date table and using the time intelligence function in DAX to create a measure.

However, if you do not have a Dim-Date table, please check the below picture and the attached pbix file.

Create a calculated column in the table, that is the Month Number CC, and then create measures like below.

NS total: =

SUM( Sheet3[NS] )

NS 6 months rolling: =

VAR currentyearmonth_sortID =

MAX ( Sheet3[Year] ) * 12

+ MAX ( Sheet3[Month Number CC] )

VAR newtable =

ADDCOLUMNS (

ALL ( Sheet3 ),

"@NSTotal", [NS total:],

"@sortID",

CALCULATE ( MAX ( Sheet3[Year] ) * 12 + MAX ( Sheet3[Month Number CC] ) )

)

VAR filternewtable =

FILTER (

newtable,

[@sortID] >= currentyearmonth_sortID - 5

&& [@sortID] <= currentyearmonth_sortID

)

RETURN

SUMX ( filternewtable, [@NSTotal] )

[Vera_33]

Hi @Anonymous 

Do you need a DAX Calculated column? And the logic is about June and differentiate years?

Cum_NS = 
VAR CurMonth =  MONTH(Sheet3[Date])
RETURN
IF(CurMonth<7, SUMX(FILTER(Sheet3,Sheet3[Year]=EARLIER(Sheet3[Year])&&MONTH(Sheet3[Date])<=CurMonth),Sheet3[NS]),
SUMX(FILTER(Sheet3,Sheet3[Year]=EARLIER(Sheet3[Year])&&MONTH(Sheet3[Date])<=CurMonth&&MONTH(Sheet3[Date])>=CurMonth-6),Sheet3[NS]))

[Anonymous]

Hi @Vera_33 ,

Thanks for the response. Yes we need calculated column and need some changes in formula

In the formula need changes.

1. We don't need to differentiate years means starting from the 7th row in table same logic shiould be applied to till the last .

For example:  in the current formula, from every every year stating month it will be recalculated . 

Expected : It shouldn't be recalculated and from 7th row it should be  be rolling 6 months sum irrespective of the year.

2. Logic is not related on June month and it should be related on the rownumber 

means if the data start from jun 2018 to 2021 nov then the first 6  rows should be cummulative after that it should be last 6 months rolling sum.

Please let me  know if you need any details

Thanks In Advance

[Vera_33]

Hi @Anonymous 

So does the table sort by date? We can add the Row number based on date?

CurRow = COUNTROWS(FILTER(Sheet3,Sheet3[Date]<=EARLIER(Sheet3[Date])))


Cum_NS = 
VAR CurR=Sheet3[CurRow]
RETURN
IF([CurRow]<7, SUMX(FILTER(Sheet3,Sheet3[CurRow]<=CurR),Sheet3[NS]),
SUMX(FILTER(Sheet3,Sheet3[CurRow]>=CurR-6&&Sheet3[CurRow]<=CurR),Sheet3[NS]))

Otherwise you need to add Index column as Row number via Power Query

Please also consider the size and the suggestion from @Jihwan_Kim