Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @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:

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

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.


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



Anonymous
Not applicable

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



Anonymous
Not applicable

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 @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:

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



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.