Forum Discussion

Nilsstigel's avatar
Nilsstigel
Frequent Visitor
4 years ago
Solved

Time intelligence - Show dates with no data on date axis

Dear all

 

I have a problem which i hope you can help me solve.

 

I am trying to visualize open vacancies (talent recruitment) over time - could be month or by date.

 

I have following dataset

NameRegistrationDateApplicationDeadlineNumberofVacancies
A01-01-202201-03-20221
B01-01-202201-03-20221
C01-02-202201-04-20221
D01-06-202201-08-20223

 

and the visual that i want to build is seen on the picture below:

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Nilsstigel ,

    Please refer to my pbix file to see if it helps you.

    Create a new table.

    Months =
DISTINCT (
    SELECTCOLUMNS (
        CALENDARAUTO (),
        "MonthNo.", MONTH ( [Date] ),
        "Month", FORMAT ( [Date], "MMM" )
    )
)

    Then create a measure.

    Measure =
CALCULATE (
    SUM ( 'Table'[NumberofVacancies] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        SELECTEDVALUE ( Months[MonthNo.] ) >= MONTH ( 'Table'[RegistrationDate] )
            && SELECTEDVALUE ( Months[MonthNo.] ) <= MONTH ( 'Table'[ApplicationDeadline] )
    )
)

    Or a column.

    column =
CALCULATE (
    SUM ( 'Table'[NumberofVacancies] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        Months[MonthNo.] >= MONTH ( 'Table'[RegistrationDate] )
            && Months[MonthNo.] <= MONTH ( 'Table'[ApplicationDeadline] )
    )
)

    Best Regards
    Community Support Team _ Polly

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Nilsstigel ,

    Please refer to my pbix file to see if it helps you.

    Create a new table.

    Months =
DISTINCT (
    SELECTCOLUMNS (
        CALENDARAUTO (),
        "MonthNo.", MONTH ( [Date] ),
        "Month", FORMAT ( [Date], "MMM" )
    )
)

    Then create a measure.

    Measure =
CALCULATE (
    SUM ( 'Table'[NumberofVacancies] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        SELECTEDVALUE ( Months[MonthNo.] ) >= MONTH ( 'Table'[RegistrationDate] )
            && SELECTEDVALUE ( Months[MonthNo.] ) <= MONTH ( 'Table'[ApplicationDeadline] )
    )
)

    Or a column.

    column =
CALCULATE (
    SUM ( 'Table'[NumberofVacancies] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        Months[MonthNo.] >= MONTH ( 'Table'[RegistrationDate] )
            && Months[MonthNo.] <= MONTH ( 'Table'[ApplicationDeadline] )
    )
)

    Best Regards
    Community Support Team _ Polly

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.