Ignore blank in calculation add column

[bbburcu]

Hi all,

I have a question for you. I hope, I'll be answered from you quickly. 

As you see attached, I have been calculated previous three rows sum, but I have a condition unfourtunately. My condition is row has until  blank previous three rows get sum. 

My formula is below,

Column 4 = CALCULATE(SUMX('Table','Table'[Column 2]), DATESINPERIOD(('Table'[Tarih].[Date]),

        STARTOFMONTH(DATEADD( 'Table'[Tarih].[Date], -3, MONTH )),3,MONTH),FILTER('Table','Table'[Tarih].[Date] < EARLIER('Table'[Tarih])),FILTER('Table','Table'[Tarih]>= CALCULATE(MIN('Date'[Date]))),'Table'[category]=EARLIER('Table'[category]))

How do I calculate ignore blanks?

I need show below,

Many thanks.

Regards.

[v-yalanwu-msft · Accepted solution]

Hi, @bbburcu ;

First you could create a month number column which  have a sort column.

Month number = MONTH( CONVERT("2022-" &[Month]&"-1",DATETIME))

The create a column by dax.

Column = 
IF([Month number]<4,BLANK(),
SUMX(
SUMMARIZE(
 TOPN(3,
      FILTER('Table',([Column 2]<>BLANK()||[Column 2]<>0) && [Month number]<EARLIER('Table'[Month number])),
      'Table'[Month number],DESC),
      [Column 2])
      ,[Column 2]))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bbburcu]

Hi @v-yalanwu-msft ,

Thank you very much! It's work. 

Best wishes,

Burcu

[amitchandak]

@bbburcu , Assuming you are need a new column, rolling 3 months

New column =

var _date1 = eomonth([Date],-3)

var _date2 = eomonth([Date],-1) +1

return

Sumx(filter(Table, [Date] >= _date1 && [Date] <= _date2), [Column2])

if you need a measure

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Table[date_closed]),-3,MONTH))

Rolling Months Formula: https://youtu.be/GS5O4G81fww

[bbburcu]

Hi @amitchandak ,

It doesn't work. I have been calculated rolling 3 months ( as you see Column 4) but I have a condition. 

If row is blank, it is not including blank row but total (any three previous row).

As you can see below how can I calculate?