cancel
Showing results for
Did you mean:
Highlighted
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

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

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

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