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.
Hi,
I have the data in the form of Fiscal Quarter and Amount as below. I do not have the date characteristics in the data. How to calculate the the YoY and QTD based on the Fiscal Quarter dynamically.
Fisc Qtr Amount
2018-Q1 10,000
2018-Q2 15,000
2018-Q3 18,000
2018-Q4 17,500
2019-Q1 20,000
2019-Q2 21,000
2019-Q3 18,000
2019-Q4 19,000
2020-Q1 20,000
2020-Q2 22,000
Thanks,
Hasmath
Solved! Go to Solution.
The first way is you create a date like this and join it with date table
date = Date(left([Fisc Qtr],4),right([Fisc Qtr],1)*3,1)
With date calendar use following formula
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))
2nd Option is
Create a Qtr table. And create a Qtr Rank and use that get formula
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),Filter(all(QTR),QTR[Rank]=Max(QTR[Rank]))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),Filter(all(QTR),QTR[Rank]=Max(QTR[Rank])-1)
Refer to how week rank work. Same is true for qtr :https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
The first way is you create a date like this and join it with date table
date = Date(left([Fisc Qtr],4),right([Fisc Qtr],1)*3,1)
With date calendar use following formula
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))
2nd Option is
Create a Qtr table. And create a Qtr Rank and use that get formula
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),Filter(all(QTR),QTR[Rank]=Max(QTR[Rank]))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),Filter(all(QTR),QTR[Rank]=Max(QTR[Rank])-1)
Refer to how week rank work. Same is true for qtr :https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
I would split out that first column into Year and Quarter columns using RIGHT and LEFT functions in calculated columns in DAX. See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
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 |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |