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 All,
I have a table contains values from July 2018 to June 2021.
This data about Tickets creation. "Ticket Number" is a primary column. I do have "Creation Date" column.
I need to calculate changes of total counts of "Tickets Number" in the Fiscal Year. Formual to calculate changes is "Previous FY - Current FY / Current FY".
I need to grep values sapartelty for following periods.
FY18-19 = values for Jul 2018 to Jun 2019.
FY19-20 = values for Jul 2019 to Jun 2020.
FY20-21 = values for Jul 2020 to Jun 2021.
Requires two help
1. Total counts for each Fiscal Year Period.
2. Measure or Calculated Column to calculate changes.
Any help will be appreciated.
Solved! Go to Solution.
Hi @sdhn ,
I created some data:
Here are the steps you can follow:
1. Create calculated column.
FY =
SWITCH(
TRUE(),
'Table'[date]>=DATE(2018,7,1)&&'Table'[date]<DATE(2019,7,1),"FY2018 - FY2019",
'Table'[date]>=DATE(2019,7,1)&&'Table'[date]<DATE(2020,7,1),"FY2019 - FY2020",
'Table'[date]>=DATE(2020,7,1)&&'Table'[date]<DATE(2021,7,1),"FY2020 - FY2021"
)
2. Create measure.
FY2018 - FY2019 =
COUNTX(FILTER(ALL('Table'),'Table'[FY]="FY2018 - FY2019"),[Ticket Number])
FY2019 - FY2020 =
COUNTX(FILTER(ALL('Table'),'Table'[FY]="FY2019 - FY2020"),[Ticket Number])
FY2020 - FY2021 =
COUNTX(FILTER(ALL('Table'),'Table'[FY]="FY2020 - FY2021"),[Ticket Number])
Change1 =
DIVIDE([FY2019 - FY2020]-[FY2018 - FY2019],[FY2018 - FY2019])
Change2 =
DIVIDE([FY2020 - FY2021]-[FY2019 - FY2020],[FY2019 - FY2020])
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @sdhn ,
I created some data:
Here are the steps you can follow:
1. Create calculated column.
FY =
SWITCH(
TRUE(),
'Table'[date]>=DATE(2018,7,1)&&'Table'[date]<DATE(2019,7,1),"FY2018 - FY2019",
'Table'[date]>=DATE(2019,7,1)&&'Table'[date]<DATE(2020,7,1),"FY2019 - FY2020",
'Table'[date]>=DATE(2020,7,1)&&'Table'[date]<DATE(2021,7,1),"FY2020 - FY2021"
)
2. Create measure.
FY2018 - FY2019 =
COUNTX(FILTER(ALL('Table'),'Table'[FY]="FY2018 - FY2019"),[Ticket Number])
FY2019 - FY2020 =
COUNTX(FILTER(ALL('Table'),'Table'[FY]="FY2019 - FY2020"),[Ticket Number])
FY2020 - FY2021 =
COUNTX(FILTER(ALL('Table'),'Table'[FY]="FY2020 - FY2021"),[Ticket Number])
Change1 =
DIVIDE([FY2019 - FY2020]-[FY2018 - FY2019],[FY2018 - FY2019])
Change2 =
DIVIDE([FY2020 - FY2021]-[FY2019 - FY2020],[FY2019 - FY2020])
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@sdhn , with date table you can use time intellignece
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"6/30"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"6/30"))
If you already have FY define in calendar
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))
Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
32 | |
30 | |
18 | |
18 |