Number of open issues between two dates

[Anonymous]

Hello Friends,

Maybe someone can help me?

I have a very easy table with 3 colums: One with the workers ID, a second one with the date of the opening of a report (dossier,issue) and a third column with the closing date of the report (dossier or issue).

I want to create a graph (and then a table) to show each month the number of dossier that the workers had open at each month.

Do you know how code this?

Thank you and best regards

A.G.

[sokg]

Can you provide us with sample data???

[Anonymous]

Hello,

I send you an example of the file.

And I want to display in a graph how many reports are open each month. 

Thank you!

Worker	Report Id	Start date	Cosing date
Anthony	200029723	26/01/2016 16:55:59	29/08/2016 14:23:08
Martin	200029080	04/01/2016 18:10:24	17/08/2017 14:47:11
Martin	200029330	14/01/2016 09:46:56	25/02/2016 15:53:21
James	200029587	21/01/2016 16:43:13	05/09/2016 15:44:24
Anthony	200029637	22/01/2016 16:44:54	31/05/2016 12:40:22
Anthony	200029873	01/02/2016 11:42:08	12/01/2017 15:45:42
James	200030330	15/02/2016 15:33:24	22/06/2016 14:37:43
Martin	200030609	23/02/2016 16:42:35	07/06/2016 11:22:21
James	200029780	27/01/2016 18:21:58	08/06/2016 13:03:11

[v-danhe-msft]

Hi @Anonymous,

Due to I could not figure the logic form your description, could you please offer me more information and post your desired result if possible?

Regards,

Daniel He

[Anonymous]

Hello Friends,

thank you for your answer. What I´m looking for is for a histogram, maybe. And what I want to display is only the open reports ( open, but not yet closed within the month. So that I would if I take my example of dataset I would display somenthing like this .

Thank you!

A.G.

[sokg]

Do you use a calendar table???

[Anonymous]

Hi,

at the moment the only table I have is similar to what I sent you.

Thank you

[sokg · Accepted solution]

I have a solution but it's very difficult.

First you need a calendar table.

Use below code to create one in power query editor.

let
    StartDate = BeginDate,
    EndDate = DateTime.Date(DateTime.FixedLocalNow()),
    //Used for 'Offset' Column calculations, you may Hard code CurrentDate for testing e.g. #date(2017,9,1) 
    CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
    // Specify the last month in your Fiscal Year, e.g. if June is the last month of your Fiscal Year, specify 6
    FiscalYearEndMonth = 6,
    #"==SET PARAMETERS ABOVE==" = 1,
    #"==Build Date Column==" = #"==SET PARAMETERS ABOVE==",
    ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    // As far as Power BI is concerned, the 'Date' column is all that is needed 🙂 But we will continue and add a few Human-Friendly Columns
    #"Changed Type to Date" = Table.TransformColumnTypes(#"Renamed Columns as Date",{{"Date", type date}}),
    #"==Add Calendar Columns==" = #"Changed Type to Date",
    #"Added Calendar MonthNum" = Table.AddColumn(#"==Add Calendar Columns==", "MonthNum", each Date.Month([Date]), Int64.Type),
    #"Added Month Name" = Table.AddColumn(#"Added Calendar MonthNum", "Month", each Text.Start(Date.MonthName([Date],"en-US"),3), type text),
    #"Added Month Name Long" = Table.AddColumn(#"Added Month Name", "MonthLong", each Date.MonthName([Date]), type text),
    #"Added Calendar Quarter" = Table.AddColumn(#"Added Month Name Long", "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date]))),
    #"Added Calendar Year" = Table.AddColumn(#"Added Calendar Quarter", "Year", each Date.Year([Date]), Int64.Type),
    #"==Add Fiscal Calendar Columns==" = #"Added Calendar Year",
    #"Added FiscalMonthNum" = Table.AddColumn(#"==Add Fiscal Calendar Columns==", "FiscalMonthNum", each if [MonthNum] > FiscalYearEndMonth
then [MonthNum] - FiscalYearEndMonth
else [MonthNum] + (12 - FiscalYearEndMonth), type number),
    #"Added FiscalMonth Name" = Table.AddColumn(#"Added FiscalMonthNum", "FiscalMonth", each [Month]),
    #"Added FiscalMonth Name Long" = Table.AddColumn(#"Added FiscalMonth Name", "FiscalMonthLong", each [MonthLong]),
    #"Added FiscalQuarter" = Table.AddColumn(#"Added FiscalMonth Name Long", "FiscalQuarter", each "FQ" & Text.From(Number.RoundUp([FiscalMonthNum] / 3,0))),
    #"Added FiscalYear" = Table.AddColumn(#"Added FiscalQuarter", "FiscalYear", each "FY" & 
Text.End(
  Text.From(
    if [MonthNum] > FiscalYearEndMonth
    then [Year] + 1
    else [Year]
  )
  , 2
)),

    #"==Add Calendar Date Offset Columns==" = #"Added FiscalYear",
    // Can be used to for example to show the past 3 months(CurMonthOffset = 0, -1, -2)
    #"Added CurMonthOffset" = Table.AddColumn(#"==Add Calendar Date Offset Columns==", "CurMonthOffset", each ( Date.Year([Date]) - Date.Year(CurrentDate) ) * 12
+ Date.Month([Date]) - Date.Month(CurrentDate), Int64.Type),
    // Can be used to for example to show the past 3 quarters (CurQuarterOffset = 0, -1, -2)
    #"Added CurQuarterOffset" = Table.AddColumn(#"Added CurMonthOffset", "CurQuarterOffset", each /*Year Difference*/
       ( Date.Year([Date]) - Date.Year(CurrentDate) )*4
       /*Quarter Difference*/
      + Number.RoundUp(Date.Month([Date]) / 3) 
      - Number.RoundUp(Date.Month(CurrentDate) / 3),
Int64.Type),
    // Can be used to for example to show the past 3 years (CurYearOffset = 0, -1, -2)
    #"Added CurYearOffset" = Table.AddColumn(#"Added CurQuarterOffset", "CurYearOffset", each Date.Year([Date]) - Date.Year(CurrentDate), Int64.Type),
    // Can be used to for example filter out all future dates
    #"Added FutureDate Flag" = Table.AddColumn(#"Added CurYearOffset", "FutureDate", each if [Date] > CurrentDate then "Future" else "Past" ),
    #"==Add General Columns==" = #"Added FutureDate Flag",
    // Used as 'Sort by Column' for MonthYear columns
    #"Added MonthYearNum" = Table.AddColumn(#"==Add General Columns==", "MonthYearNum", each [Year]*100 + [MonthNum] /*e.g. Sep-2016 would become 201609*/, Int64.Type),
    #"Added MonthYear" = Table.AddColumn(#"Added MonthYearNum", "MonthYear", each [Month] & "-" & Text.End(Text.From([Year]),2)),
    #"Added MonthYearLong" = Table.AddColumn(#"Added MonthYear", "MonthYearLong", each [Month] & "-" & Text.From([Year])),
    #"Added WeekdayNum" = Table.AddColumn(#"Added MonthYearLong", "WeekdayNum", each Date.DayOfWeek([Date]), Int64.Type),
    #"Added Weekday Name" = Table.AddColumn(#"Added WeekdayNum", "Weekday", each Text.Start(Date.DayOfWeekName([Date]),3), type text),
    #"Added WeekdayWeekend" = Table.AddColumn(#"Added Weekday Name", "WeekdayWeekend", each if [WeekdayNum] = 5 or [WeekdayNum] = 6
then "Weekend"
else "Weekday"),
    #"==Improve Ultimate Table" = #"Added WeekdayWeekend",
    #"----Add WeekSequenceNum----" = #"==Improve Ultimate Table",
    #"Filtered Rows Sundays Only (Start of Week)" = Table.SelectRows(#"----Add WeekSequenceNum----", each ([WeekdayNum] = 6)),
    #"Added Index WeekSequenceNum" = Table.AddIndexColumn(#"Filtered Rows Sundays Only (Start of Week)", "WeekSequenceNum", 2, 1),
    #"Merged Queries Ultimate Table to WeekSequenceNum" = Table.NestedJoin(#"==Improve Ultimate Table",{"Date"},#"Added Index WeekSequenceNum",{"Date"},"Added Index WeekNum",JoinKind.LeftOuter),
    #"Expanded Added Index WeekNum" = Table.ExpandTableColumn(#"Merged Queries Ultimate Table to WeekSequenceNum", "Added Index WeekNum", {"WeekSequenceNum"}, {"WeekSequenceNum"}),
    // somehow it ends up being unsorted after Expand Column, should not matter for the end table, but makes it harder to debug and check everything is correct. Thus sorting it.
    #"ReSorted Rows by Date" = Table.Sort(#"Expanded Added Index WeekNum",{{"Date", Order.Ascending}}),
    #"Filled Down WeekSequenceNum" = Table.FillDown(#"ReSorted Rows by Date",{"WeekSequenceNum"}),
    #"Replaced Value WeekSequenceNum null with 1" = Table.ReplaceValue(#"Filled Down WeekSequenceNum",null,1,Replacer.ReplaceValue,{"WeekSequenceNum"}),
    #"----WeekSequenceNum Complete----" = #"Replaced Value WeekSequenceNum null with 1",
    Current_WeekSequenceNum = #"----WeekSequenceNum Complete----"{[Date = CurrentDate]}?[WeekSequenceNum],
    #"Added Custom CurWeekOffset" = Table.AddColumn(#"----WeekSequenceNum Complete----", "CurWeekOffset", each [WeekSequenceNum] - Current_WeekSequenceNum, Int64.Type),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom CurWeekOffset",{{"Quarter", type text}, {"FiscalMonth", type text}, {"FiscalMonthLong", type text}, {"FiscalQuarter", type text}, {"FiscalYear", type text}, {"FutureDate", type text}, {"MonthYear", type text}, {"MonthYearLong", type text}, {"WeekdayWeekend", type text}})
in
    #"Changed Type"

Then create theese measures

FirstDate = 
IF (
    ISCROSSFILTERED ( dcalendar[Date] );
    MIN ( dcalendar[Date] );
    TODAY ()
)

LastDate = 
IF (
    ISCROSSFILTERED ( dcalendar[Date] );
    MAX ( dcalendar[Date] );
    TODAY ()
)

OpenItems = 
AVERAGEX (
    DATESBETWEEN ( dcalendar[Date]; [FirstDate]; [LastDate] );
    SUMX (
        FILTER (
            CALCULATETABLE (
                ADDCOLUMNS (
                    SUMMARIZE ( Table1; Table1[Start date]; Table1[Closing date] );
                    "NumOfOrders"; CALCULATE ( COUNTROWS ( Table1 ) )
                );
                ALL ( dcalendar )
            );
            AND ( Table1[Start date] <= dcalendar[Date]; Table1[Closing date] >= dcalendar[Date] )
        );
        [NumOfOrders]
    )
)

then create a column chart and put MonthYear on axis and OpenItems on values.

The output is (see pic below)

I found the sololution at

https://www.sqlbi.com/daxpuzzle/active-orders/

and then click view solution.

Hopefully this is what you are looking for!