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

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?