Dear All,
Kindly help me create below SQL Query to DAX or Power Query in Power BI.
I am completely new to this. Truely appreciate your help.
SELECT t2.Transdate, t2.HOURREADINGS AS MinHourReading,
t3.Transdate, t3.HOURREADINGS AS MaxHourReading
, abs(t3.HOURREADINGS - t2.HOURREADINGS) as MonthlyReading
, t1.EquipmentNo, t3.projid
FROM
( SELECT EquipmentNo, min(Transdate) as minTransDate, max(Transdate) as maxTransDate
FROM [dbo].[LTFUELJOURNALHISTORY]
WHERE EquipmentNo = 'G15_05' --and Transdate between '2019-08-01' and '2019-08-31'
GROUP BY EquipmentNo, MONTH(Transdate), YEAR(Transdate)
) AS t1
JOIN [dbo].[LTFUELJOURNALHISTORY] AS t2 ON t2.Transdate = t1.minTransDate and t1.EquipmentNo = t2.EquipmentNo
JOIN [dbo].[LTFUELJOURNALHISTORY] AS t3 ON t3.Transdate = t1.maxTransDate and t1.EquipmentNo = t3.EquipmentNo
ORDER BY t1.EquipmentNo, YEAR(t3.Transdate), MONTH(t3.Transdate)
