Sort Order with Time intelligence - Fiscal year

[chanal]

Hello,

I have an issue with Time Intelligence when I want to sort my fiscal year this way:

I was able to sort my fiscal year to have the most recent one (30/09/18) as rank 1 and the other 30/09/17 as rank 2. But when I do that , the formulas made by Quick measures / Time Intelligence / Year over year change / does not work any more .... is there another way to do that, in order for me to keep sorted my fiscal year and to have year to year change ? 

Plus: I do not like when you have Column blank for the 1st year change. => how to remove this column with nothing in it?

see my exemple to download via we transfer : https://bit.ly/2FD3ipo  

Thank you so much for your precious help !!!!

Isabelle

[v-qiuyu-msft · Accepted solution]

Hi @chanal,

You can create measures below: 

DiffBalance = 
VAR predate =
    CALCULATE (
        MAX ( 'Table2'[FiscalYear] ),
        FILTER (
            ALL ( Table2 ),
            'Table2'[Compte] = MAX ( Table2[Compte] )
                && 'Table2'[FiscalYear] < MAX ( Table2[FiscalYear] )
        )
    )
VAR preBalance =
    CALCULATE (
        MAX ( Table2[Balance] ),
        FILTER (
            ALL ( 'Table2' ),
            'Table2'[Compte] = MAX ( Table2[Compte] )
                && 'Table2'[FiscalYear] = predate
        )
    )
RETURN
    IF (
        ISINSCOPE ( Table2[FiscalYear] ),
        IF ( predate = BLANK (), BLANK (), MAX ( Table2[Balance] ) - preBalance ),
        SUM ( Table2[Balance] )
            - CALCULATE (
                SUM ( Table2[Balance] ),
                FILTER (
                    ALL ( 'Table2' ),
                    'Table2'[FiscalYear] = MIN ( 'Table2'[FiscalYear] )
                        && Table2[Compte] = MAX ( Table2[Compte] )
                )
            )
    )

Diff% = 
VAR predate =
    CALCULATE (
        MAX ( 'Table2'[FiscalYear] ),
        FILTER (
            ALL ( Table2 ),
            'Table2'[Compte] = MAX ( Table2[Compte] )
                && 'Table2'[FiscalYear] < MAX ( Table2[FiscalYear] )
        )
    )
VAR preBalance =
    CALCULATE (
        MAX ( Table2[Balance] ),
        FILTER (
            ALL ( 'Table2' ),
            'Table2'[Compte] = MAX ( Table2[Compte] )
                && 'Table2'[FiscalYear] = predate
        )
    )
RETURN
    IF (
        ISINSCOPE ( Table2[FiscalYear] ),
        IF (
            predate = BLANK (),
            BLANK (),
            DIVIDE ( MAX ( Table2[Balance] ) - preBalance, preBalance )
        ),
        DIVIDE (
            SUM ( Table2[Balance] )
                - CALCULATE (
                    SUM ( Table2[Balance] ),
                    FILTER (
                        ALL ( 'Table2' ),
                        'Table2'[FiscalYear] = MIN ( 'Table2'[FiscalYear] )
                            && Table2[Compte] = MAX ( Table2[Compte] )
                    )
                ),
            CALCULATE (
                SUM ( Table2[Balance] ),
                FILTER (
                    ALL ( 'Table2' ),
                    'Table2'[FiscalYear] = MIN ( 'Table2'[FiscalYear] )
                        && Table2[Compte] = MAX ( Table2[Compte] )
                )
            )
        )
    )

>> Plus: I do not like when you have Column blank for the 1st year change. => how to remove this column with nothing in it?

Currently, there is no OOTB feature for to achieve this requirement. To work around the issue, we can turn off Word Wrap option for column headers, then decrease column header width for these blank columns. 

You can test with attached pbix file. 

Best Regards,
Qiuyun Yu 

[chanal]

Thank you so much for your answer !!!😀

Best regards