I'm new to Power BI and DAX. I'm not from an Excel background either but a SQL one so not overly familiar with a lot of the DAX functions/methods.
I have the following SQL query...
create table #Metrics
(
dt date,
product varchar(30),
value numeric(9,2),
measure varchar(50)
)
declare @loopstart date
declare @loopend date
declare @thisdate date
set @loopend = dateadd(day, datediff(day, 0, getdate()), 0)
set @loopstart = dateadd(year, -1, dateadd(month, datediff(month, 0, getdate()), 0))
set @thisdate = @loopstart
while (@thisdate <= @loopend)
begin
insert into #Metrics
select
@thisdate,
x.ITMGEDSC,
count(*),
'MonthFails'
from
(
select
f.IncidentId,
f.createdon,
f.new_completeddate,
f.new_gpitemnumber,
f.new_serialnumber,
i.ACTLSHIP,
f.new_gpcustomerid,
datediff(day, i.ACTLSHIP, f.new_completeddate) daystofail,
i.ITMGEDSC
from
Failures f join
Installs i on i.ITMGEDSC = left(f.new_gpitemnumber, 6) and f.new_gpcustomerid = i.CUSTNMBR and f.new_serialnumber = i.SERLTNUM
where
f.FailureType = 'Filtered' and
f.new_completeddate between @thisdate and eomonth(@thisdate)
) as x
where
x.ACTLSHIP between dateadd(year, -1, @thisdate) and dateadd(day, -1, @thisdate)
group by
x.ITMGEDSC
insert into #Metrics
select
@thisdate,
x.ITMGEDSC,
count(*),
'Fails'
from
(
select
f.ticketnumber,
f.createdon,
f.new_completeddate,
f.new_gpitemnumber,
f.new_serialnumber,
i.ACTLSHIP,
f.new_gpcustomerid,
datediff(day, i.ACTLSHIP, f.new_completeddate) daystofail,
i.ITMGEDSC
from
Failures f join
Installs i on i.ITMGEDSC = left(f.new_gpitemnumber, 6) and f.new_gpcustomerid = i.CUSTNMBR and f.new_serialnumber = i.SERLTNUM
where
f.FailureType = 'Filtered' and
f.new_completeddate between dateadd(month, 1, dateadd(year, -1, @thisdate)) and eomonth(@thisdate)
) as x
where
x.ACTLSHIP between dateadd(year, -1, @thisdate) and dateadd(day, -1, @thisdate)
group by
x.ITMGEDSC
insert into #Metrics
select @thisdate dt, ITMGEDSC, count(*) installs, 'Installs' from Installs where ACTLSHIP between dateadd(year, -1, @thisdate) and dateadd(day, -1, @thisdate) group by ITMGEDSC
set @thisdate = dateadd(month, 1, @thisdate)
end;
select i.dt, i.Product, i.value as Installs, isnull(f.value, 0) as Fails, isnull(mf.Value, 0) as MonthFails from #Metrics i left join #Metrics f on i.dt = f.dt and f.measure = 'Fails' and i.Product = f.Product left join #Metrics mf on i.dt = mf.dt and i.Product = mf.Product and mf.measure = 'MonthFails' where i.Measure = 'Installs'
drop table #Metrics
which outputs data like so...
| dt | Product | Installs | Fails | MonthFails |
| 01/04/2018 | D | 2094 | 205 | 25 |
| 01/04/2018 | C | 1258 | 151 | 19 |
| 01/04/2018 | M | 7 | 0 | 0 |
| 01/05/2018 | D | 2102 | 198 | 16 |
| 01/05/2018 | C | 1289 | 168 | 24 |
| 01/05/2018 | M | 9 | 0 | 0 |
| 01/06/2018 | D | 2073 | 198 | 29 |
| 01/06/2018 | C | 1326 | 184 | 26 |
| 01/06/2018 | M | 15 | 0 | 0 |
| 01/07/2018 | D | 2018 | 200 | 25 |
| 01/07/2018 | C | 1320 | 177 | 17 |
| 01/07/2018 | M | 28 | 2 | 1 |
| 01/08/2018 | D | 2052 | 231 | 47 |
| 01/08/2018 | C | 1335 | 187 | 27 |
| 01/08/2018 | M | 39 | 2 | 0 |
| 01/09/2018 | D | 2130 | 244 | 29 |
| 01/09/2018 | C | 1338 | 182 | 11 |
| 01/09/2018 | M | 52 | 4 | 2 |
| 01/10/2018 | D | 2138 | 249 | 39 |
| 01/10/2018 | C | 1367 | 185 | 23 |
| 01/10/2018 | M | 82 | 15 | 7 |
| 01/11/2018 | D | 2192 | 281 | 40 |
| 01/11/2018 | C | 1418 | 206 | 30 |
| 01/11/2018 | M | 91 | 18 | 3 |
| 01/12/2018 | D | 2218 | 288 | 24 |
| 01/12/2018 | C | 1420 | 216 | 40 |
| 01/12/2018 | M | 92 | 20 | 1 |
| 01/01/2019 | D | 2195 | 286 | 32 |
| 01/01/2019 | C | 1450 | 213 | 21 |
| 01/01/2019 | M | 108 | 26 | 6 |
| 01/02/2019 | D | 2049 | 261 | 13 |
| 01/02/2019 | C | 1454 | 207 | 19 |
| 01/02/2019 | M | 135 | 30 | 3 |
| 01/03/2019 | D | 1914 | 220 | 20 |
| 01/03/2019 | C | 1536 | 225 | 30 |
| 01/03/2019 | M | 155 | 36 | 5 |
| 01/04/2019 | D | 1881 | 204 | 21 |
| 01/04/2019 | C | 1610 | 212 | 26 |
| 01/04/2019 | M | 180 | 38 | 2 |
So I've pulled the Failures and Installs tables into Power BI with the aim of replicating the above. I created a new merged query between the Failures and Installs tables called Failures wtih Installs which was efectively a left join between Failures and Installs.
In Power query, I created a new calculated column in the Failures with Installs table to determine if the product was installed within 12 months of it failing.
if [InWarrantyFails_Installs.ACTLSHIP] >= Date.AddYears([CompletedMonth], -1) and [InWarrantyFails_Installs.ACTLSHIP] <= Date.AddDays([CompletedMonth], -1) then true else false
I then created a MonthFails measure in the Failures with Installs table like so;
MonthFails = CALCULATE(COUNTROWS('Failures with Installs'),'Failures with Installs'[InWarrantyFail] = TRUE(),GROUPBY('Failures with Installs','Failures with Installs'[CompletedMonth]))
This appears to work and gives me numbers which match those in the SQL output for the MonthFails column. What I'm struggling with is the Fails column which is a cumulative total over a date range.
I'm not even sure if the way I'm trying to do ALL of this is the correct way. I did start out by basically using my SQL as the query directly. But this limited me when it came to slicing data.
