topic Re: How to write a dax to Get cumulative sum till 6th row and after 6th row rolling 6 months sum ? in DAX Commands and Tips

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

Anonymous — Tue, 23 Nov 2021 18:50:40 GMT

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

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

Anonymous — Wed, 24 Nov 2021 00:44:23 GMT

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]))

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

Jihwan_Kim — Wed, 24 Nov 2021 04:19:02 GMT

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] )

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

Anonymous — Wed, 24 Nov 2021 04:22:00 GMT

Hi Anonymous ,

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

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

Anonymous — Wed, 24 Nov 2021 05:24:26 GMT

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