Forum Discussion

bbburcu's avatar
bbburcu
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 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.

 

Need Help
  • v-yalanwu-msft's avatar
    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      bbburcu
      Frequent Visitor
      3 years ago

      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?

       

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity 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.