How to: Calculate day-to-day stock based on a filter variable that is changing

[Anonymous]

Hi,

Let's say I have a table containing job candidates searching for jobs. The first few rows of the table could look like this:

|     id       |     In Date     |     Status     |     Out Date      |

-----------------------------------------------------------

      1             1 April            Looking       

      2             2 April        Not Looking      15 May

      3             2 April            Looking        

      4             3 April            Looking        

      5             4 April        Not Looking      16 May

-----------------------------------------------------------

*In Date = the date the candidate was registered as a candidate

*Out Date = the date the candidate got a job and is no longer looking for a new job

*Status = indicates if the candidate is looking for a new job or not 

The 'Status' column is manually changed from 'Looking' to 'Not Looking' when candidates have gotten a job. What I want to do is make a bar chart with X axis = all the days in current month, and Y axis = Stock (# of candidates actively looking for a new job). The stock should go up/down from day to day, f.x. like this: 

The problem is that the bars in my chart is only increasing from day to day. This is because I use the 'Status' column as filter when I calculate the Stock. So when a candidate is suddenly 'Not Looking' for a new job, this also affects the Stock bars from previous days. The way I calculate my stock is that I have made a Stock column in my Date table. It looks like this:

Stock = IF('Calendar'[Date] < TODAY();
CALCULATE(COUNTROWS('table'); 
    'table'[Out Date] = BLANK();
    'table'[Status] <> "Not Looking";
    'Calendar'[Date]  <= EARLIER ('Calendar'[Date])   
    );
    BLANK() )

I would really appreciate if anyone could help me solve this.

Cheers

[sdjensen · Accepted solution]

Hi @Anonymous

Perhaps you should try to reorganize your data in your table to calculate the correct stock for any given day.

Based on your demo data I created the following table:

Using the following code: The first row is purely manual input of your demo data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1DUw0TUyMLQAcnzy87Mz89KBLKVYnWglI5C8EZK8X36JAkKNoamugSlECqTaGF01mmkmIHlj3PKmIHkT3LaZIWyLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, #"In Date" = _t, Status = _t, #"Out Date" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"In Date", type date}, {"Status", type text}, {"Out Date", type date}}),
    InDate = Table.SelectColumns(ChangedType,{"Id", "In Date"}),
    InDate_InOut = Table.AddColumn(InDate, "In/Out", each 1),
    InDate_RenameColumn = Table.RenameColumns(InDate_InOut,{{"In Date", "Date"}}),
    
    OutDate = Table.SelectColumns(Table.SelectRows(ChangedType, each [Out Date] <> null), {"Id", "Out Date"}),
    OutDate_InOut = Table.AddColumn(OutDate, "In/Out", each -1),
    OutDate_RenameColumn = Table.RenameColumns(OutDate_InOut,{{"Out Date", "Date"}}),
    
    AppendInOut = Table.Combine({InDate_RenameColumn, OutDate_RenameColumn}),
    ChangedType2 = Table.TransformColumnTypes(AppendInOut,{{"In/Out", Int64.Type}})
in
    ChangedType2

I also added a basic period table, that is needed for the time intelligense calculation - I used this code for the period table:

let
    Source = List.Dates,
    InvokedFunction = Source(#date(2018, 1, 1), Duration.Days(#date(2018, 12, 31) - #date(2018,1,1)) + 1, #duration(1, 0, 0, 0)),
    TableFromList = Table.FromList(InvokedFunction, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    RenamedColumn1 = Table.RenameColumns(TableFromList,{{"Column1", "Date"}}),
    ChangedTypeDate = Table.TransformColumnTypes(RenamedColumn1,{{"Date", type date}}),
    AddYear = Table.AddColumn(ChangedTypeDate, "Year", each Date.Year([Date])),
    AddQuarterNumber = Table.AddColumn(AddYear, "QuarterNumber", each Date.QuarterOfYear([Date]), Int64.Type),
    AddQuarter = Table.AddColumn(AddQuarterNumber, "Quarter", each "K" & Number.ToText([QuarterNumber])),
    AddMonthNumber = Table.AddColumn(AddQuarter, "MonthNumber", each Date.Month([Date])),
    AddMonth = Table.AddColumn(AddMonthNumber, "Month", each Date.ToText([Date],"MMM")),
    AddDayOfMonth = Table.AddColumn(AddMonth, "Day", each Date.Day([Date])),
    AddYearQuarter = Table.AddColumn(AddDayOfMonth, "YearQuarter", each "K" & Number.ToText([QuarterNumber]) & " " & Number.ToText([Year])),
    AddYearQuarterSorting = Table.AddColumn(AddYearQuarter, "YearQuarterNumber", each Number.ToText([Year]) & Text.End("0"&Number.ToText([QuarterNumber]), 2)),
    AddYearMonth = Table.AddColumn(AddYearQuarterSorting, "YearMonth", each Date.ToText([Date],"MMM") & " " & Number.ToText([Year])),
    AddYearMonthSorting = Table.AddColumn(AddYearMonth, "YearMonthNumber", each Number.ToText([Year]) & Text.End("0"&Number.ToText([MonthNumber]), 2)),
    AddDayOfWeekNumber = Table.AddColumn(AddYearMonthSorting, "DayOfWeekNumber", each Date.DayOfWeek([Date], Day.Monday) + 1),
    AddDayOfWeekLabel = Table.AddColumn(AddDayOfWeekNumber, "Weekday", 
        each if [DayOfWeekNumber] = 1 then "Monday"
        else if [DayOfWeekNumber] = 2 then "Thuesday"
        else if [DayOfWeekNumber] = 3 then "Wednesday"
        else if [DayOfWeekNumber] = 4 then "Thursday"
        else if [DayOfWeekNumber] = 5 then "Friday"
        else if [DayOfWeekNumber] = 6 then "Saturday"
        else "Sunday"),
    ChangeTypes = Table.TransformColumnTypes(AddDayOfWeekLabel,
        {
            {"MonthNumber", Int64.Type}, 
            {"YearQuarterNumber", Int64.Type}, 
            {"YearMonthNumber", Int64.Type}, 
            {"Year", type text}, 
            {"Month", type text}, 
            {"Quarter", type text}, 
            {"Day", Int64.Type}, 
            {"YearQuarter", type text}, 
            {"YearMonth", type text}, 
            {"DayOfWeekNumber", Int64.Type}, 
            {"Weekday", type text}
        })
in
    ChangeTypes

I loaded the data into the model and made a relationship between the 2 tables.

Then you can create your DAX formula to calculate your "Stock"

Stock = 
IF(
    MIN( Period[Date] ) <= [MaxDate];
    CALCULATE(
        SUM( Table1[In/Out] );
        DATESBETWEEN( Period[Date]; BLANK(); MAX( Period[Date] ) )
    )
)

MaxDate is calculated like this: The point of MaxDate is just to stop the Stock calculation to return a result for all future dates in your period table.

MaxDate = CALCULATE( MAX( Table1[Date] ); ALL( Period ) )

After this I am able to make this barchart - which as far as I understand if the one you are looking for.

[Anonymous]

Hello again @sdjensen, 

Your solution is still running strong, but I am facing a minor issue in my graph. It seems that for a given month with number of days less than 31, f.x. April 2019 with 30 days, my graph adds a bar for day 31 (which should not be possible). This bar shows the same value as the current date value.

Any idea how I can fix this?

Thank you.

Kr

[sdjensen]

Hi @Anonymous,

Are you sure it's April 31th you are seeing and not an unknown member? Please try to hover your mouse on on the column to check this. If it's an unknown member then it's most likely because you have data in your model on dates that is not included in your calendar table, hence this will cause these kind of calculations to "fail" and return an unknown member with the sum of all transactions.

Could you please try to use a real data coloum (from your calendar table) instead of day of month number in your visual.

[Anonymous]

Hi @sdjensen,

Impressive - this did the trick! Thank you very much for a thorough and precise explanation, I appreciate it a lot.

Have a nice day. Until next time 😉

Best,

Per

[afzalphatan]

Try this

CALCULATE ( COUNTROWS ( table ),  table[Status] = "Looking"  )