Create a bar char showing only specific data periods

[Anonymous]

Hi all,

I need to create a bar chart, but I just want to show you a few months.
These months depend on the selection made on the slicer.
The calculation of the value of each month has the particularity mentioned in the image.
I really don't now how to do it.
I appreciate your help.

[v-lionel-msft]

Hi @Anonymous ,

Please provide a sample .pbix file.

Best regards,
Lionel Chen

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

[MFelix]

Hi @Anonymous ,

Altough you don't present any data setup I assume you have a calendar table and you data has a start and end date, also don't know if you have the table related in either case you need to have a calculation based on a disconnected table this can be done in two ways.

With Disconnected date table

Add the following measure:

HEADCOUNT =
CALCULATE (
    Table[ID],
    FILTER (
        ALLSELECTED ( Table[ID], Table[Start Date], Table[End Date] ),
        Table[Start Date] <= MIN ( Calendar[Date] )
            && Table[End Date] >= MAX ( Calendar[Date] )
    )
)

Related Tables (Calendar[DATE] and TRable[StartDate]

Create the following measure:

HEADCount =
CALCULATE (
    Table[ID],
    FILTER (
        ALLSELECTED ( Table[ID], Table[Start Date], Table[End Date] ),
        Table[StartDate] <= MIN ( Calendar[Date] )
            && Table[End Date] >= MAX ( Calendar[Date] )
    ),
    CROSSFILTER ( Calendar[Date], Table[StartDate], NONE )
)

On the X-axis and in the slicer use the Calendar table column.

[Anonymous]

Hi @MFelix ,

Thanks for the help, but I probably didn't make myself understood.
I want to select a date in the slicer and I want some month valuesalways appear, for example:

Imagine that I'm selecting March 2020.
I want to get the values for January and August 2019 and also the value for the month before the one selected.

Thanks

[MFelix]

What are the months you want to show? Is the selected month, previous months and the others are random or fixed? 

[Anonymous]

Hi @MFelix 

If  selected Year <> Current Year then,
 - show Jan and Dez previous Year
If  selected Year = Current Year and Actual Month Jan
 - show Jan and Dez previous Year
If  selected Year = Current Year and Actual Month = Fev
 - show Jan current year and Jan and Dez previous year
If  selected Year = Current Year and Actual Month > Fev
 - show Prev current month and Jan current Year and Jan and Dez previous year

I have already implemented a solution with a disconnected table but the problem is how the values are calculated.
The calculation of the month value is represented by the lines that are not in that month.
For example: the number of employees for January is the country of all names whose admission date is <1 January and ending contract > 31 January.
In a table and for a single month it works.
I have already thought of using a calendar table with Offset months.
What do you think?
 
Regards
Joao Monteiro

[MFelix · Accepted solution]

Hi @Anonymous ,

What I did was to create a new calendar table to use has slicer then added the following measure:

Filtering_Dates = 
VAR Current_Month =
    FILTER (
        'Calendar',
        'Calendar'[Monthyear] = SELECTEDVALUE ( 'Calendar_Filter'[Monthyear] )
    )
VAR previous_Year =
    FILTER (
        'Calendar',
        'Calendar'[Year] = YEAR ( MAX ( 'Calendar_Filter'[Date] ) )
            && MONTH ( 'Calendar'[Date] ) IN { 1, 12 }
    )
VAR January_December_Selection =
    FILTER (
        'Calendar',
        'Calendar'[Year] = MAX ( 'Calendar_Filter'[Year] )
            && MONTH ( 'Calendar'[Date] )
                IN { 1 }
                    || (
                        'Calendar'[Year]
                            = MAX ( 'Calendar_Filter'[Year] ) - 1
                            && MONTH ( 'Calendar'[Date] ) IN { 12 }
                    )
    )
VAR Previous_Month =
    FILTER (
        'Calendar',
        'Calendar'[Year] = MAX ( 'Calendar_Filter'[Year] )
            && MONTH ( 'Calendar'[Date] )
                = MONTH ( MAX ( Calendar_Filter[Date] ) ) - 1
    )
VAR FinalFilter =
    IF (
        YEAR ( MAX ( Calendar_Filter[Date] ) ) <> YEAR ( TODAY () ),
        COUNTROWS ( UNION ( Current_Month, previous_Year) ),
        IF (
            MONTH ( MAX ( Calendar_Filter[Date] ) ) <= 2,
            COUNTROWS ( UNION ( Current_Month, January_December_Selection ) ),
            COUNTROWS ( UNION ( Current_Month, January_December_Selection, Previous_Month ) )
        )
    )
RETURN
    FinalFilter

Result below and in attach file: