Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PowerQA1
Frequent Visitor

Multiple Calculated fields displaying monthly stacked column chart

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

  • Axis - Detected On Date (Date defect was found)
  • Value - Count of Detected on Date (Count of Defects)

 

Second calculates the number of test cases executed monthly - using the stacked column chart

  • Axis - Executed Date (Date cases were executed)
  • Value - Test Case Count (Count of executed test cases)

 

Now I need to calculate the TCE monthly which is the Monthly Defects / Monthly Cases Executed and plot this monthly in a bar chart

  • Using the stacked column chart can't get the monthly numbers to calculate correctly

TCE Calc

  • Axis - Detected On Date (Date defect was found)
  • Value - ILI Defect Count divided by ILI Case Count (DAX Below)

DAX

ILI Defect Count divided by ILI Case Count = DIVIDE([ILI Defect Count], [ILI Case Count])

 

Results Showing in Graph

  • Jan - .70%
  • Feb - .57%
  • Mar - .54%
  • Apr - 1.13%
  • May - .22%

 

If I switch the Axis from Detected On Date (Date defect was found) to Executed Date (Date cases were executed)

Results Showing in Graph

  • Jan - 14.22%
  • Feb - 20.65%
  • Mar - 11.83%
  • Apr - 16.37%
  • May - 19.39%

 

Correct Values

  • Jan - 3.1% (45/1435)
  • Feb - 3.7% (37/988)
  • Mar - 2.0% (35/1724)
  • Apr - 5.8% (73/1246)
  • May - 1.3% (14/1052)

Not sure what to do any ideas?

1 ACCEPTED 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

View solution in original post

8 REPLIES 8
PowerQA1
Frequent Visitor

Thanks for your help...

 

My company does not allow me to share files.

 

Here is some sample data

 

 

There are two tables

  • One table has typical Defect data
  • One table has typical Execution data

Defect Data Table Sample

 

Defect IDApplicationProvderProjectReleasePriorityStatusSystemSummaryDetected On 
726App 1Comp 1Changes OutputFebruary3-HighClosedOutput Bad Output1/31/2019
888App 2Comp 2Input ChangesMay - 195-UrgentClosedInputBad Input3/12/19

 

 

Exectution Data Table Sample

 

Application Test IDExec DateTest: Execution StatusNameProject ReleaseStatusSubject
App18565311/23/2019PassedTest 1Project 1JanPassedSubject 1
App28556251/10/2019PassedTest 2Project 218L-December+PassedSubject 2
App38546181/18/2019PassedTest 3Project 319B-FebruaryPassedSubject 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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

TCE Chart Values if Exec Date Field used as AxisTCE Chart Values if Exec Date Field used as Axis

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

 

 

Detected On Date Field Used in TCE ChartDetected On Date Field Used in TCE Chart

The TCE chart

  • Is just a simple division of the defects / test cases
  • It's not working because they are two differnt tables of data & the chart is using the date field to display them monthly
  • DAX used in the Value Field - TCE = IF(ISBLANK([Defect Count]/'TCE'[Case Count),0,[Defect Count]/'TCE'[Case Count])

 

 

 

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)

Ashish_Mathur
Super User
Super User

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.