Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Rolling 3 months calculation

Hi I am looking at creating a DAX measure which looks at the waste weight value column (Table Data) to see if there is any data and if not calculates a rolling 3 month to give me information as to whether the site has submitted or not submitted thier data. The Table data shows the initial table view and the intended results table shows the Rolling 3 months results i am looking for. Thnaks in advance for your help. 

 

 

Table DataIntended Results Table

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , with help from a date tbale try measure like

     

    Rolling -3 =
    Var _1 = CALCULATE(sum(Table[ waste weight value]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date])-1,-3,MONTH))
    return
    if(isblank(_1) || _1 =0 , "Not Submitted", "Submitted")

     

    or


    Rolling -3 =
    var _max = MAX('Date'[Date])-1
    var _min = Date(year(_max), month(_max)-3, day(_max))
    Var _1 = CALCULATE(sum(Table[ waste weight value]),filter(all('Date') , 'Date'[Date] >=Min && 'Date'[Date] <=_max))
    return
    if(isblank(_1) || _1 =0 , "Not Submitted", "Submitted")

     

     

    use date from date table in visual

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Anonymous ;

    You could try it.

    Measure = 
var _dif=DATEDIFF( MINX(ALL('Table'),[Month]),MAX([Month]),MONTH)
var _cout=CALCULATE(COUNT([Waste Weight value]),
            FILTER(ALLEXCEPT('Table','Table'[Site ID]),FORMAT( MAX('Table'[Waste Weight value]),"string")<>BLANK()&&
                        [Month]<=MAX([Month])&&EOMONTH([Month],0)>EOMONTH(MAX([Month]),-3)))
var _b= IF(_dif>=2, IF(_cout>=3,"Submitted","Not Submitted"))
return _b

    The final output is shown below:

    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
    4 years ago

    Anonymous , with help from a date tbale try measure like

     

    Rolling -3 =
    Var _1 = CALCULATE(sum(Table[ waste weight value]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date])-1,-3,MONTH))
    return
    if(isblank(_1) || _1 =0 , "Not Submitted", "Submitted")

     

    or


    Rolling -3 =
    var _max = MAX('Date'[Date])-1
    var _min = Date(year(_max), month(_max)-3, day(_max))
    Var _1 = CALCULATE(sum(Table[ waste weight value]),filter(all('Date') , 'Date'[Date] >=Min && 'Date'[Date] <=_max))
    return
    if(isblank(_1) || _1 =0 , "Not Submitted", "Submitted")

     

     

    use date from date table in visual

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      How would i go about creating a count to count how many of the sites have submitted that month in order to create trend graphs to see in which months how many sites submitted data and how many didnt? 

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous ;

    You could try it.

    Measure = 
var _dif=DATEDIFF( MINX(ALL('Table'),[Month]),MAX([Month]),MONTH)
var _cout=CALCULATE(COUNT([Waste Weight value]),
            FILTER(ALLEXCEPT('Table','Table'[Site ID]),FORMAT( MAX('Table'[Waste Weight value]),"string")<>BLANK()&&
                        [Month]<=MAX([Month])&&EOMONTH([Month],0)>EOMONTH(MAX([Month]),-3)))
var _b= IF(_dif>=2, IF(_cout>=3,"Submitted","Not Submitted"))
return _b

    The final output is shown below:

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    How would i go about creating a count to count how many of the sites have submitted that month in order to create trend graphs to see in which months how many sites submitted data and how many didnt?