Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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.
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?
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 , 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
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?
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |