cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 145 members 1,575 guests
Please welcome our newest community members: