Two line graph across a non-standard year

[Anonymous]

Hi all,

Complete Power BI newbie here.  I'm trying to create a line graph that shows cumulative values for two years as separate lines.  From previous help topics and a bit of messing around, I've created the following measure:

Cumulative value = 
IF(
    MIN( Dates[Date] )
        <= CALCULATE( MAX( Dates[Date] ), ALL( Data )),
    CALCULATE(
        SUM( Data[Values] ),
        FILTER( ALL( Dates ),
            Dates[Date] <= MAX( Dates[Date] ) && YEAR( Dates[Date] ) = YEAR( MAX( Dates[Date] ))
        )
    )
)

Which almost works, except that I'm not looking for a graph that runs from January - December.  My resulting graph  is this:

(Axis: Month - Legend: Year - Values: The above calculated field)  

So to my question -  Is there an easy way to replicate the functionality of the filter in the above measure in a way that respects the custom month order in my date table? Basically, I'm looking to have the "reset point" be August, rather than December.

Thanks in advance for any help.

[v-shex-msft]

Hi @Anonymous ,

>>Is there an easy way to replicate the functionality of the filter in the above measure in a way that respects the custom month order in my date table? 

In fact, measure is impossible to effect axis sorting order.

In my opinion, I'd like to suggest you to modify 'sort by column' of month field or add a custom sort order table for this scenario.

Sort by column in Power BI Desktop

Custom Sorting in Power BI

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Hi @v-shex-msft,

Thanks for getting back to me.  Your comment, plus a good night's sleep and my morning coffee, helped me realise that the answer was more simple than I was thinking.

I already had a custom sort for month which was working perfectly, I just needed to add a custom "year order" column and refer to that directly rather than using YEAR.

Cumulative value = 
IF(
    MIN( Dates[Date] )
        <= CALCULATE( MAX( Dates[Date] ), ALL( Data )),
    CALCULATE(
        SUM( Data[Values] ),
        FILTER( ALL( Dates ),
            Dates[Date] <= MAX( Dates[Date] ) && YEAR( Dates[Date] ) = YEAR( MAX( Dates[Date] ))
            Dates[Date] <= MAX( Dates[Date] ) && Dates[Year Order] = MAX( Dates[Year Order] )
        )
    )
)

Results in the graph changing to :

Thanks for your assistance!