Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Thank you so much for your answer !!!😀
Best regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |