Forum Discussion

chanal's avatar
chanal
Icon for Helper I rankHelper I
7 years ago
Solved

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

Reports
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    7 years ago

    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 

2 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      chanal
      Icon for Helper I rankHelper I
      6 years ago

      Thank you so much for your answer !!!😀

       

      Best regards