Forum Discussion

Frequent Visitor

3 years ago

Solved

Ignore blank in calculation add column

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 cond...

Need Help

v-yalanwu-msft
3 years ago
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.
Ignore blank in calculation add column.pbix39 KB

v-yalanwu-msft

Community Support

3 years ago

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.

Ignore blank in calculation add column.pbix39 KB