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 one issue with this formula regarding Variation in values between dates , when the 1st period of variation is 0.
Table 1 :
Result with the issue :
so , in the 2nd row, the variation between 2016 and 2015 should have been +100 instead of blank.
Do you know how to do so with my formulas please :
Here is my formulas :
Calculated column :
Thank you in advance
Solved! Go to Solution.
@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 @Sky571 ,
I have few questions areound the formula you have used. In the Var Value you have used:
DIVIDE( CY - PY, PY)* PY which essentially translate to CY - PY.
What is you are trying to achieve?
Can you please help me with the expected output and if possible with sample data?
Cheers!
Vivek
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 :
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
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
53 | |
46 | |
15 | |
12 |