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

Create a bar char showing only specific data periods

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.

JoaoMonteiro_0-1614085046253.png

 

1 ACCEPTED SOLUTION

Hi @JoaoMonteiro ,

 

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:

MFelix_0-1614334840452.pngMFelix_1-1614334860923.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
v-lionel-msft
Community Support
Community Support

Hi @JoaoMonteiro ,

 

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
Super User III
Super User III

Hi @JoaoMonteiro ,

 

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

Hi @JoaoMonteiro ,

 

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:

MFelix_0-1614334840452.pngMFelix_1-1614334860923.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors