Need Help in Creating the chart as per explained below

[Sanmaya1]

Need to Create a chart which shows LateBacklock and month from next month onwards with fiscal year. Lateback log Quantities are the quantity which are pervious months Quantity and current Month Quantity. below is the Chart for reference and my fiscal month starts from October till September. hence for fiscal year 2025 the Month start from Oct instead of Jan, feb etc.

[Sanmaya1]

Hi, Need help in Creating as chart as the x axis will so the  fiscal month and Year. and condition is that my fiscal year start from October of every year. Take the example no i am in June then the chart will show all past value as Late and the Value from July 2024 it will show the late quantities till September, when calendar  month  October will show as Oct-2025 instead of Oct-2024.

below is the example.

[Anonymous · Accepted solution]

@lbendlin , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:

Hi  @Sanmaya1 ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
var _table1=
DISTINCT('Table'[Year])
var _table2=
{"Late Backlog"}
var _table3=
CROSSJOIN(

    _table1,_table2)

var _table4=
SUMMARIZE('Table',[Year],[Month])
var _table5=
UNION(
    _table4,_table3)
return
ADDCOLUMNS(
    _table5,"Date",
    IF(
        [Month]="Late Backlog",BLANK(),
        MINX(FILTER(ALL('Table'),
        [Year]=EARLIER('Table'[Year])&&[Month]=EARLIER('Table'[Month])),[Date])))

2. Create measure.

Flag =
VAR _today =
    TODAY ()
VAR _startdate =
    EOMONTH ( _today, 0 )
VAR _enddate =
    EOMONTH ( _today, 3 )
VAR _nextenddate =
    DATE ( YEAR ( _enddate ) + 1, MONTH ( _enddate ), 1 )
RETURN
    IF (
        MAX ( 'Table 2'[Month] ) = "Late Backlog"
            || MAX ( 'Table 2'[Date] ) > _startdate
                && MAX ( 'Table 2'[Date] ) <= _enddate
            || MAX ( 'Table 2'[Date] ) > _nextenddate,
        1,
        0
    )

Value_Measure =
VAR _today =
    TODAY ()
VAR _startdate =
    EOMONTH ( _today, 0 )
VAR _enddate =
    EOMONTH ( _today, 3 )
RETURN
    IF (
        MAX ( 'Table 2'[Month] ) = "Late Backlog",
        SUMX (
            FILTER (
                ALL ( 'Table' ),
                [Year] = MAX ( 'Table 2'[Year] )
                    && 'Table'[Date] <= _enddate
            ),
            [Value]
        ),
        SUMX (
            FILTER (
                ALL ( 'Table' ),
                [Year] = MAX ( 'Table 2'[Year] )
                    && [Month] = MAX ( 'Table 2'[Month] )
            ),
            [Value]
        )
    )

3. Place [Flag]in Filters, set is=1, apply filter.

4. Because Power BI's default sorting is alphabetical, we need to create a sort table if we want to show the desired effect.

Table 3 =
SUMMARIZE(
    'Table 2',[Month],
    "date",
    IF(
        [Month]="Late Backlog",MINX(ALL('Table 2'),[Date])-1,
    MINX(FILTER(ALL('Table 2'),[Month]=EARLIER('Table 2'[Month])),[Date])))

Select [Month] – Column tools – Sort by column – [date]

5. Joining two tables.

6. Result:

Best Regards,

Liu Yang

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

Because

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...