topic Re: Need Help in Creating the chart as per explained below in DAX Commands and Tips

Need Help in Creating the chart as per explained below

Sanmaya1 — Sun, 23 Jun 2024 11:32:05 GMT

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.

Re: Need Help in Creating the chart as per explained below

Sanmaya1 — Sun, 23 Jun 2024 14:51:41 GMT

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.

Re: Need Help in Creating the chart as per explained below

lbendlin — Sun, 23 Jun 2024 18:10:55 GMT

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-Forum/ba-p/963216

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/1447523

Re: Need Help in Creating the chart as per explained below

Anonymous — Tue, 25 Jun 2024 07:38:02 GMT

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