help :Clustered column chart

[Anonymous]

I have requirement to develop below chart in PBI,

Here The line should be as below criteria:

• This would have Actuals value till the YTD months. For the future months, this would be a flat line with value = monthly average of actuals for the YTD months

Please help me how to achieve this.

Thanks in advance

[v-lionel-msft]

Hi @Anonymous ,

Does the line in the chart represent the [Average] column?

I don't quite understand the calculation logic of [Average] column. Could you give me the Mathematical calculation formula?

Or you can do like this:

First, sort [Month] column in ascending order, then add an [index] column in Edit Queries.

Second, create [Average] measure:

Average = 
VAR x= 
CALCULATE(
    SUM(Sheet7[Actuals]),
    FILTER(
        ALLSELECTED(Sheet7),
        [Index] <= MAX(Sheet7[Index])
    )
)
RETURN
IF(
    MAX(Sheet7[Index]) > 2,
    125,
    x / MAX(Sheet7[Index])
)

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.

[amitchandak]

In case you are using date calendar, you can use datesytd or datesqtd

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin