Padding a graph with dummy values

[Anonymous]

Hi Experts,

I have a Department tables wit h departments data , some departments have data since 2018 Jan, but some only have from 2019 Jan.

Requirement is that even when a department does not have existance in a 2018 the graph should display date from 2018 Jan .Is this possible?

Sample Data

Departments	Headcount	Report Date
Department 1	200	Jan-18
Department 1	250	Jan-19
Department 2	20	Jan-18
Department 2	23	Jan-19
Department 3	30	Jan-19

Thanks 

DJ

[sturlaws · Accepted solution]

Hi @Anonymous

I would say it is possible, but your specification is a bit short. How do you want to visualize this? With department on axis, or just the total of all departments and time? Do you just want to see the values for january 2018 and january 2019, or do you want to se the values between as well? Which value should be used for a department which has no value in january 2018, 0 or the value from january 2019?

Here is a version that will work if you have department on axis or legend, and want to use the 2019 value if 2018 is blank.

number of employees number from next year =
VAR firstYear =
    CALCULATE ( MIN ( Department[Report Date] ); ALL ( 'date'[Report Date] ) )
VAR firstValue =
    CALCULATE (
        SUM ( Department[Headcount] );
        FILTER ( ALL ( 'date'[Report Date] ); 'date'[Report Date] = firstYear )
    )
RETURN
    IF (
        ISBLANK ( SUM ( Department[Headcount] ) );
        firstValue;
        SUM ( Department[Headcount] )
    )

Cheers,
Sturla