Hi All,
I am using the report server DB - Execution Log table.
I am trying to get the count of hits of report in a selected quarter.
The catch is, the hits have to be calculated for only those reports which are created in the previous month.
Eg : A report was created in Feb, then the usage/hits have to be calculated from March.
I am able to give the correct counts for monthly, but for Quarter, the hits aren't correct.
I am using the below logic :
// Get Minimum Month yyyymm from selected Quarter
var _selected_yymm_start = CALCULATE(MIN(Usage[Data_YYYYMM]), Usage[Quarter] = SELECTEDVALUE(Usage[Quarter]))
//Calculate Hits for 1st Mth in Qtr
var __Listofreports_m1 = CALCULATETABLE(VALUES(Usage[Report path]), Usage[Creation_YYYYMM] < _selected_yymm_start)
var _hits_m1 = CALCULATE(SUMX(FILTER(Usage,Usage[Report path] in __Listofreports_m1), Usage[Total Hits Monthly]))
//Calculate Hits for 2nd Mth in Qtr
var __Listofreports_m2 = CALCULATETABLE(VALUES(Usage[Report path]), Usage[Creation_YYYYMM] < _selected_yymm_start+1)
var _hits_m2 = CALCULATE(SUM(Usage[Total Hits Monthly]), Usage[Report path] in __Listofreports_m2, Usage[Data_YYYYMM] = _selected_yymm_start+1)
//Calculate Hits for 3rd Mth in Qtr
var __Listofreports_m3 = CALCULATETABLE(VALUES(Usage[Report path]), Usage[Creation_YYYYMM] < _selected_yymm_start+2)
var _hits_m3 = CALCULATE(SUM(Usage[Total Hits Monthly]), Usage[Report path] in __Listofreports_m3, Usage[Data_YYYYMM] = _selected_yymm_start+1)
var _total_hits = _hits_m1+_hits_m2+_hits_m3
return
_hits_m1
===========================================================
Usage : Table with Month level hits of reports
Total Hits Monthly : Column which has the hits of the report.
Data_YYYYMM : Hits YYYYMM data
Creation_YYYYMM : Report Creation date in YYYYMM format
The expectation is to have the individual monthly hits match the monthly hits measure (used in another visual):
Total Hits =
var _hits_old = SUM(Usage[Total Hits Monthly])
//Get the YYYYMM format from selected Slicers
var _year = SELECTEDVALUE(Usage[Year_Index])
var _mth = SELECTEDVALUE(Usage[Month Name])
var _mth_num = maxx(FILTER((Usage), Usage[Month Name] = _mth), Usage[Month Num])
var _selected_date = 100*_year + _mth_num
//Get list of reports to be considered for the selected slicers
var __Listofreports = CALCULATETABLE(VALUES(Usage[Report path]), Usage[Creation_YYYYMM] < _selected_date)
//Hits for the above list of reports only
var _hits = CALCULATE(SUM(Usage[Total Hits Monthly]), Usage[Report path] in __Listofreports)
return
if(_hits = BLANK(), 0, _hits)
Thanks in advance for your support.
