Rolling 3 months calculation

[Anonymous]

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

[amitchandak · Accepted solution]

@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]

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 · Accepted solution]

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.

[amitchandak · Accepted solution]

@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]

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?