cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
chanal Frequent Visitor
Frequent Visitor

Sort Order with Time intelligence - Fiscal year

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

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: Sort Order with Time intelligence - Fiscal year

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] )
                )
            )
        )
    )

q5.PNG

 

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Moderator v-qiuyu-msft
Moderator

Re: Sort Order with Time intelligence - Fiscal year

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] )
                )
            )
        )
    )

q5.PNG

 

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.