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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JohanSmith7
Frequent Visitor

How to format dynamic X axis in a Bar Chart

Hi all, I have a table with demand values in different dates and need a bar chart that changes the labels in x axis based on the date selected in a filter. I have attached Sample data. 

 

Let's say the user selects a date in May 2023, so the bar chart should show the next 6 months of data and also show grouped in a label "> Months" the demand values for months after Oct-2023 and also it should group the previous 6 months of demand data with a label "< 6 months". The x axis labels of the chart should change based on the date selected.

 

Details of the logic if May-2023 is selected in a filter:

JohanSmith7_0-1686070489623.png

Expected result:

JohanSmith7_1-1686070659425.png

 

Is there a way to achieve it to have dynamic labels based on the selected date? I have tried with conditional columns but I am getting static results.

 

Thanks in advance,

 

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @JohanSmith7 ,

 

Please try:

First create a new table for x-axis:

X-axis = 
var _a = DISTINCT(SELECTCOLUMNS('Table',"YearMonth",FORMAT([Date],"MMM-YY"),"Rank",RANKX('Table',YEAR([Date])*100+MONTH([Date]),,ASC,Dense)))
var _b = COUNTROWS(_a)
return UNION(_a,{("> 6 months",_b),("< 6 months",0)})

vjianbolimsft_1-1686299912940.png

Then apply this measure to the chart:

Measure =
VAR _a =
    MIN ( 'Table'[Date] )
VAR _b =
    SELECTCOLUMNS (
        CALENDAR ( _a, EDATE ( _a, 5 ) ),
        "YearMonth", FORMAT ( [Date], "MMM-YY" )
    ) //6 months
VAR _c =
    CALCULATE (
        SUM ( 'Table'[Demand] ),
        FILTER (
            ALL ( 'Table' ),
            FORMAT ( [Date], "MMM-YY" ) = SELECTEDVALUE ( 'X-axis'[YearMonth] )
        )
    ) // calculate value for selected month
VAR _d =
    CALCULATE ( SUM ( 'Table'[Demand] ), FILTER ( ALL ( 'Table' ), [Date] < _a ) ) //calculate value for < 6months
VAR _e =
    CALCULATE (
        SUM ( 'Table'[Demand] ),
        FILTER ( ALL ( 'Table' ), [Date] > EOMONTH ( _a, 5 ) )
    ) //calculate value for > 6months
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'X-axis'[YearMonth] ) IN _b,
            CALCULATE (
                SUM ( 'Table'[Demand] ),
                FILTER (
                    ALL ( 'Table' ),
                    FORMAT ( [Date], "MMM-YY" ) = SELECTEDVALUE ( 'X-axis'[YearMonth] )
                )
            ),
        SELECTEDVALUE ( 'X-axis'[YearMonth] ) = "< 6 months", _d,
        SELECTEDVALUE ( 'X-axis'[YearMonth] ) = "> 6 months", _e
    )

vjianbolimsft_2-1686300456932.png

Final output:

vjianbolimsft_3-1686300471959.png

Best Regards,

Jianbo Li

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

View solution in original post

3 REPLIES 3
v-jianboli-msft
Community Support
Community Support

Hi @JohanSmith7 ,

 

Please try:

First create a new table for x-axis:

X-axis = 
var _a = DISTINCT(SELECTCOLUMNS('Table',"YearMonth",FORMAT([Date],"MMM-YY"),"Rank",RANKX('Table',YEAR([Date])*100+MONTH([Date]),,ASC,Dense)))
var _b = COUNTROWS(_a)
return UNION(_a,{("> 6 months",_b),("< 6 months",0)})

vjianbolimsft_1-1686299912940.png

Then apply this measure to the chart:

Measure =
VAR _a =
    MIN ( 'Table'[Date] )
VAR _b =
    SELECTCOLUMNS (
        CALENDAR ( _a, EDATE ( _a, 5 ) ),
        "YearMonth", FORMAT ( [Date], "MMM-YY" )
    ) //6 months
VAR _c =
    CALCULATE (
        SUM ( 'Table'[Demand] ),
        FILTER (
            ALL ( 'Table' ),
            FORMAT ( [Date], "MMM-YY" ) = SELECTEDVALUE ( 'X-axis'[YearMonth] )
        )
    ) // calculate value for selected month
VAR _d =
    CALCULATE ( SUM ( 'Table'[Demand] ), FILTER ( ALL ( 'Table' ), [Date] < _a ) ) //calculate value for < 6months
VAR _e =
    CALCULATE (
        SUM ( 'Table'[Demand] ),
        FILTER ( ALL ( 'Table' ), [Date] > EOMONTH ( _a, 5 ) )
    ) //calculate value for > 6months
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'X-axis'[YearMonth] ) IN _b,
            CALCULATE (
                SUM ( 'Table'[Demand] ),
                FILTER (
                    ALL ( 'Table' ),
                    FORMAT ( [Date], "MMM-YY" ) = SELECTEDVALUE ( 'X-axis'[YearMonth] )
                )
            ),
        SELECTEDVALUE ( 'X-axis'[YearMonth] ) = "< 6 months", _d,
        SELECTEDVALUE ( 'X-axis'[YearMonth] ) = "> 6 months", _e
    )

vjianbolimsft_2-1686300456932.png

Final output:

vjianbolimsft_3-1686300471959.png

Best Regards,

Jianbo Li

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

v-jianboli-msft
Community Support
Community Support

Hi @JohanSmith7 ,

 

The number of months between 2023-May and 2022-Jun is greater than 6.
How to determine the date <6 months, is it based on the number of dates in your data?
Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

 

Best Regards,

Jianbo Li

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

 

 

Hi @v-jianboli-msft, yes the dates "< 6 months" and "> 6 months" should be determined based on the dates y my data.

I only have one column with dates "LAGWEEK".

Every time the user selects a date, the chart should:

  1. Show 6 months of data beginning the month selected by the user.
  2. Show < 6 Months --> this should sum data before the date selected by user.
  3. Show > 6 Months--> this should sum data after the date selected. See the screenshot below:

JohanSmith7_0-1686233223990.png

Hopefully the previous details helps to clarify what the chart should show,

Regards,

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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