Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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]))
Try using the Running Total quick measure in the Desktop. Otherwise, you could also take a look at my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Also, if coming from a SQL background, then Phil Seamark's book, Beginning DAX with Power BI is the book for you. https://www.amazon.com/Beginning-DAX-Power-BI-Intelligence/dp/1484234766
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |