Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Guys,
Newbie here to DAX.. Appreciate any help you can give.
I have two calculations which are working fine.
One calculates the number of defects written monthly - using the stacked column chart
Second calculates the number of test cases executed monthly - using the stacked column chart
Now I need to calculate the TCE monthly which is the Monthly Defects / Monthly Cases Executed and plot this monthly in a bar chart
TCE Calc
DAX
Results Showing in Graph
If I switch the Axis from Detected On Date (Date defect was found) to Executed Date (Date cases were executed)
Results Showing in Graph
Correct Values
Not sure what to do any ideas?
Solved! Go to Solution.
Here is a query you can use to create a calendar. Link the Date field from your table to the Month field from the calendar. This should help with your calculation.
= (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertFinYearNumber = Table.AddColumn(InsertMonth, "FinYearNumber",each if [MonthOfYear] >= 7 then [Year] else [Year] -1 ),
InsertFinYearDisplay = Table.AddColumn(InsertFinYearNumber, "FinYear" ,each "FY" & Text.End(Number.ToText([FinYearNumber],"D",""),2) & "/" & Text.End(Number.ToText([FinYearNumber]+1,"D",""),2)),
InsertFinMonth = Table.AddColumn(InsertFinYearDisplay, "FinMonth", each if [MonthOfYear] >= 7 then [MonthOfYear] - 6 else [MonthOfYear] + 6 ),
InsertFinQuarterNumber = Table.AddColumn(InsertFinMonth, "FinQuarterNumber", each if [QuarterOfYear] > 2 then [QuarterOfYear] -2 else [QuarterOfYear] + 2),
InsertFinQuarterDisplay = Table.AddColumn(InsertFinQuarterNumber ,"FinQuarter", each "FQ" & Number.ToText([FinQuarterNumber],"D","") ),
InsertDay = Table.AddColumn(InsertFinQuarterDisplay , "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date],1)+1),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEndingFriday", each Date.EndOfWeek([Date],6), type date),
InsertCurrentThursday = Table.AddColumn(InsertWeekEnding, "CurrentThursday", each Date.AddDays([Date], -Date.DayOfWeek([Date],1) + 3), type date),
InsertISOWeekJan4 = Table.AddColumn(InsertCurrentThursday, "ISOWeekJan4", each Date.FromText(Number.ToText(Date.Year([CurrentThursday])) & "-01-04") ,type date),
InsertISOWeekYear = Table.AddColumn(InsertISOWeekJan4, "ISOWeekYear", each Date.Year([CurrentThursday])) ,
InsertISOWeekFirstMon = Table.AddColumn(InsertISOWeekYear, "ISOWeekFirstMon", each
if [CurrentThursday] < [ISOWeekJan4]
then Date.AddDays([CurrentThursday],-3)
else Date.AddDays([ISOWeekJan4], - Date.DayOfWeek([ISOWeekJan4],1) )
,type date),
InsertISOWeekNum = Table.AddColumn(InsertISOWeekFirstMon, "ISOWeekNum", each Number.RoundUp(((Duration.Days(Duration.From([Date] - [ISOWeekFirstMon]))+1) /7 )), type number),
InsertISOWeekID = Table.AddColumn(InsertISOWeekNum, "ISOWeekID", each [ISOWeekYear] * 100 + [ISOWeekNum], type number),
InsertISOWeekName = Table.AddColumn(InsertISOWeekID, "ISOWeekName", each Text.From([ISOWeekYear]) & "W" & Text.End( "0" & Text.From(([ISOWeekNum]*10) + [DayInWeek]),3)),
InsertISOWeekNameLong = Table.AddColumn(InsertISOWeekName, "ISOWeekNameLong", each Text.From([ISOWeekYear]) & "-W" & Text.End( "0" & Text.From([ISOWeekNum]),2) & "-" & Text.From([DayInWeek])),
fnPeriod445a = (weekNum) => let
Periods =
{
{(x)=>x<5, [P=1,Q=1]},
{(x)=>x<9, [P=2,Q=1]},
{(x)=>x<14, [P=3,Q=1]},
{(x)=>x<18, [P=4,Q=2]},
{(x)=>x<22, [P=5,Q=2]},
{(x)=>x<27, [P=6,Q=2]},
{(x)=>x<31, [P=7,Q=3]},
{(x)=>x<35, [P=8,Q=3]},
{(x)=>x<40, [P=9,Q=3]},
{(x)=>x<44, [P=10,Q=4]},
{(x)=>x<48, [P=11,Q=4]},
{(x)=>true, [P=12,Q=4]}
},
Result = List.First(List.Select(Periods, each _{0}(weekNum))){1}
in
Result,
InsertPeriod445 = Table.AddColumn(InsertISOWeekNameLong, "Period445Record", each fnPeriod445a([ISOWeekNum])),
ExpandPeriod445 = Table.ExpandRecordColumn(InsertPeriod445, "Period445Record", {"P","Q" }, {"Period445", "Quarter445"}),
RemovedColumns = Table.RemoveColumns(ExpandPeriod445,{"CurrentThursday", "ISOWeekFirstMon"})
in
RemovedColumns
Thanks for your help...
My company does not allow me to share files.
Here is some sample data
There are two tables
Defect Data Table Sample
Defect ID | Application | Provder | Project | Release | Priority | Status | System | Summary | Detected On |
726 | App 1 | Comp 1 | Changes Output | February | 3-High | Closed | Output | Bad Output | 1/31/2019 |
888 | App 2 | Comp 2 | Input Changes | May - 19 | 5-Urgent | Closed | Input | Bad Input | 3/12/19 |
Exectution Data Table Sample
Application | Test ID | Exec Date | Test: Execution Status | Name | Project | Release | Status | Subject |
App1 | 856531 | 1/23/2019 | Passed | Test 1 | Project 1 | Jan | Passed | Subject 1 |
App2 | 855625 | 1/10/2019 | Passed | Test 2 | Project 2 | 18L-December+ | Passed | Subject 2 |
App3 | 854618 | 1/18/2019 | Passed | Test 3 | Project 3 | 19B-February | Passed | Subject 3 |
Hi,
Based on the 2 Tables shared by you, please show the expected result in a new Table (3 columns - Date and expected figure from figure 1 and expected figure from figure 2). Once the Table is ready, one can simply switch to one's desired visual.
Hi Ashsish
Thanks so much for your help, but my company does not give me access to that area to download a file.
Is there any other way you could share the information
Here is a query you can use to create a calendar. Link the Date field from your table to the Month field from the calendar. This should help with your calculation.
= (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertFinYearNumber = Table.AddColumn(InsertMonth, "FinYearNumber",each if [MonthOfYear] >= 7 then [Year] else [Year] -1 ),
InsertFinYearDisplay = Table.AddColumn(InsertFinYearNumber, "FinYear" ,each "FY" & Text.End(Number.ToText([FinYearNumber],"D",""),2) & "/" & Text.End(Number.ToText([FinYearNumber]+1,"D",""),2)),
InsertFinMonth = Table.AddColumn(InsertFinYearDisplay, "FinMonth", each if [MonthOfYear] >= 7 then [MonthOfYear] - 6 else [MonthOfYear] + 6 ),
InsertFinQuarterNumber = Table.AddColumn(InsertFinMonth, "FinQuarterNumber", each if [QuarterOfYear] > 2 then [QuarterOfYear] -2 else [QuarterOfYear] + 2),
InsertFinQuarterDisplay = Table.AddColumn(InsertFinQuarterNumber ,"FinQuarter", each "FQ" & Number.ToText([FinQuarterNumber],"D","") ),
InsertDay = Table.AddColumn(InsertFinQuarterDisplay , "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date],1)+1),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEndingFriday", each Date.EndOfWeek([Date],6), type date),
InsertCurrentThursday = Table.AddColumn(InsertWeekEnding, "CurrentThursday", each Date.AddDays([Date], -Date.DayOfWeek([Date],1) + 3), type date),
InsertISOWeekJan4 = Table.AddColumn(InsertCurrentThursday, "ISOWeekJan4", each Date.FromText(Number.ToText(Date.Year([CurrentThursday])) & "-01-04") ,type date),
InsertISOWeekYear = Table.AddColumn(InsertISOWeekJan4, "ISOWeekYear", each Date.Year([CurrentThursday])) ,
InsertISOWeekFirstMon = Table.AddColumn(InsertISOWeekYear, "ISOWeekFirstMon", each
if [CurrentThursday] < [ISOWeekJan4]
then Date.AddDays([CurrentThursday],-3)
else Date.AddDays([ISOWeekJan4], - Date.DayOfWeek([ISOWeekJan4],1) )
,type date),
InsertISOWeekNum = Table.AddColumn(InsertISOWeekFirstMon, "ISOWeekNum", each Number.RoundUp(((Duration.Days(Duration.From([Date] - [ISOWeekFirstMon]))+1) /7 )), type number),
InsertISOWeekID = Table.AddColumn(InsertISOWeekNum, "ISOWeekID", each [ISOWeekYear] * 100 + [ISOWeekNum], type number),
InsertISOWeekName = Table.AddColumn(InsertISOWeekID, "ISOWeekName", each Text.From([ISOWeekYear]) & "W" & Text.End( "0" & Text.From(([ISOWeekNum]*10) + [DayInWeek]),3)),
InsertISOWeekNameLong = Table.AddColumn(InsertISOWeekName, "ISOWeekNameLong", each Text.From([ISOWeekYear]) & "-W" & Text.End( "0" & Text.From([ISOWeekNum]),2) & "-" & Text.From([DayInWeek])),
fnPeriod445a = (weekNum) => let
Periods =
{
{(x)=>x<5, [P=1,Q=1]},
{(x)=>x<9, [P=2,Q=1]},
{(x)=>x<14, [P=3,Q=1]},
{(x)=>x<18, [P=4,Q=2]},
{(x)=>x<22, [P=5,Q=2]},
{(x)=>x<27, [P=6,Q=2]},
{(x)=>x<31, [P=7,Q=3]},
{(x)=>x<35, [P=8,Q=3]},
{(x)=>x<40, [P=9,Q=3]},
{(x)=>x<44, [P=10,Q=4]},
{(x)=>x<48, [P=11,Q=4]},
{(x)=>true, [P=12,Q=4]}
},
Result = List.First(List.Select(Periods, each _{0}(weekNum))){1}
in
Result,
InsertPeriod445 = Table.AddColumn(InsertISOWeekNameLong, "Period445Record", each fnPeriod445a([ISOWeekNum])),
ExpandPeriod445 = Table.ExpandRecordColumn(InsertPeriod445, "Period445Record", {"P","Q" }, {"Period445", "Quarter445"}),
RemovedColumns = Table.RemoveColumns(ExpandPeriod445,{"CurrentThursday", "ISOWeekFirstMon"})
in
RemovedColumns
Hi,
You may download my PBI file from here.
Hope this helps.
The TCE chart
These are the correct values which I want in the TCE Chart
Jan - 3.1% (45/1435)
Feb - 3.7% (37/988)
Mar - 2.0% (35/1724)
Apr - 5.8% (73/1266)
May - 1.3% (14/1052)
Hi,
Share the link from where i can download your PBI file.
User | Count |
---|---|
131 | |
108 | |
101 | |
67 | |
64 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |