cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
divjoy690 Frequent Visitor
Frequent Visitor

Padding a graph with dummy values

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

 

DepartmentsHeadcountReport Date
Department 1200Jan-18
Department 1250Jan-19
Department 220Jan-18
Department 223Jan-19
Department 330Jan-19

 

Thanks 

DJ

1 ACCEPTED SOLUTION

Accepted Solutions
sturlaws New Contributor
New Contributor

Re: Padding a graph with dummy values

Hi @divjoy690

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

View solution in original post

1 REPLY 1
sturlaws New Contributor
New Contributor

Re: Padding a graph with dummy values

Hi @divjoy690

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

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,274)