Forum Discussion
Issue with Variation using Time Intelligence when figure is 0
Sky571 ,
From what I have understood your scenario, I have recreated it with some dummy values:
Data table:
For using the time intelligence in Power BI, adding a date calendar is recommended. I have used the following DAX code for adding new DAX table:
ftCalendar = ADDCOLUMNS ( CALENDARAUTO (), "Year", YEAR ( [Date] ), "Month", EOMONTH ( [Date], -1 ) + 1, "Qtr", "Q" & FORMAT ( [Date], "q" ), "FY", IF ( //for financial year ending in March MONTH ( [Date] ) > 3, "FY " & YEAR ( [Date] ) & "-" & RIGHT ( YEAR ( [Date] ) + 1, 2 ), "FY " & YEAR ( [Date] ) - 1 & "-" & RIGHT ( YEAR ( [Date] ), 2 ) ), "FY Quarter", "Q" & CEILING ( MONTH ( EOMONTH ( [Date], -3 ) ), 3 ) / 3 )In this, I have included two columns for Financial year and FY quarter. In this example, the FY ends in March.
For more details on this, you may refer to the following articles:
https://www.vivran.in/post/calculating-financial-year-quarter
https://www.vivran.in/post/power-bi-time-intelligence-calendar-table
Mark the table as Date table and create the relationship between the data table and the calendar table:
Balance = SUM(dtTable[Debit]) - SUM(dtTable[Credit]) Balance Prev Month = CALCULATE([Balance],PREVIOUSMONTH(ftCalendar[Date])) Prev Year Balance = CALCULATE([Balance],SAMEPERIODLASTYEAR(ftCalendar[Date])) %Variance PY = DIVIDE([Prev Year Balance] - [Balance],[Prev Year Balance]) % Variation PM = DIVIDE([Balance Prev Month] - [Balance],[Balance Prev Month])Then you can use it in the visuals accordingly:
You may refer to the folowing articles for more details:
https://www.vivran.in/post/introduction-to-time-intelligence-part-1
https://www.vivran.in/post/dax-time-intelligence-part-2-till-date-aggregations
Cheers!
Vivek
If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
Hello vivran22 ,
Thank you for answering.
I want to keep with Time intelligence because :
- I have Fiscal Years which do not finish at the end of the year
- I want to automatize my model (do not want to change parameters in DAX for that , depending on when the fiscal years end)
The Variation in % works very well with the function DIVIDE. But for the Variation Value => i tried to remove DIVIDE, it works but now I have datas in the 1st year.
See my example :
1st solution with formula DIVIDE :
pb in the 2nd row when 0 is on the 1st year :
2nd solution with NO formula DIVIDE :
pb in the column Variation value : figures appears wrongly in the 1st year , but issue resolved concerning 0 in the 1st year
Here is my example :
https://drive.google.com/file/d/1hy2q9dx8qPkbTs1yBt5EEZHN9I_02lJP/view?usp=sharing
Thank you !
Sky571 ,
From what I have understood your scenario, I have recreated it with some dummy values:
Data table:
For using the time intelligence in Power BI, adding a date calendar is recommended. I have used the following DAX code for adding new DAX table:
ftCalendar =
ADDCOLUMNS (
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Month", EOMONTH ( [Date], -1 ) + 1,
"Qtr", "Q" & FORMAT ( [Date], "q" ),
"FY", IF (
//for financial year ending in March
MONTH ( [Date] ) > 3,
"FY " & YEAR ( [Date] ) & "-"
& RIGHT ( YEAR ( [Date] ) + 1, 2 ),
"FY "
& YEAR ( [Date] ) - 1 & "-"
& RIGHT ( YEAR ( [Date] ), 2 )
),
"FY Quarter", "Q"
& CEILING ( MONTH ( EOMONTH ( [Date], -3 ) ), 3 ) / 3
)
In this, I have included two columns for Financial year and FY quarter. In this example, the FY ends in March.
For more details on this, you may refer to the following articles:
https://www.vivran.in/post/calculating-financial-year-quarter
https://www.vivran.in/post/power-bi-time-intelligence-calendar-table
Mark the table as Date table and create the relationship between the data table and the calendar table:
Balance = SUM(dtTable[Debit]) - SUM(dtTable[Credit])
Balance Prev Month = CALCULATE([Balance],PREVIOUSMONTH(ftCalendar[Date]))
Prev Year Balance = CALCULATE([Balance],SAMEPERIODLASTYEAR(ftCalendar[Date]))
%Variance PY = DIVIDE([Prev Year Balance] - [Balance],[Prev Year Balance])
% Variation PM = DIVIDE([Balance Prev Month] - [Balance],[Balance Prev Month])
Then you can use it in the visuals accordingly:
You may refer to the folowing articles for more details:
https://www.vivran.in/post/introduction-to-time-intelligence-part-1
https://www.vivran.in/post/dax-time-intelligence-part-2-till-date-aggregations
Cheers!
Vivek
If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
Hello vivran22 ,
Thank you so much ! Yes, it is what I wanted , thank you.
It is just that it needs to add manually the end of the month for the Fiscal Year (in the Date Calendar Table)
so, it is good for users that are able to change the month, according to their customers's End of Fiscal Year.
Thank you so much for your explanation and pbix file ! Greatly appreciated !
xx