Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi @amitchandak ,
Please help me in sort out this.
I have a table which contains 3 columns
Date | Sales 1 | Sales 2 | Sales1 - Sales2 | Average / Year |
2018-01 | 1,279.00 | 603.00 | 676.00 | 653.00 |
2018-02 | 1,315.00 | 563.00 | 752.00 | 653.00 |
2018-03 | 1,301.00 | 576.00 | 725.00 | 653.00 |
2018-04 | 1,308.00 | 607.00 | 701.00 | 653.00 |
2018-05 | 1,334.00 | 673.00 | 661.00 | 653.00 |
2018-06 | 1,326.00 | 642.00 | 684.00 | 653.00 |
2018-07 | 1,300.00 | 657.00 | 643.00 | 653.00 |
2018-08 | 1,296.00 | 654.00 | 642.00 | 653.00 |
2018-09 | 1,281.00 | 690.00 | 591.00 | 653.00 |
2018-10 | 1,273.00 | 687.00 | 586.00 | 653.00 |
2018-11 | 1,152.00 | 530.00 | 622.00 | 653.00 |
2018-12 | 1,039.00 | 486.00 | 553.00 | 653.00 |
2019-01 | 1,042.00 | 484.00 | 558.00 | 439.75 |
2019-02 | 1,059.00 | 520.00 | 539.00 | 439.75 |
2019-03 | 1,068.00 | 553.00 | 515.00 | 439.75 |
2019-04 | 1,080.00 | 583.00 | 497.00 | 439.75 |
2019-05 | 1,053.00 | 557.00 | 496.00 | 439.75 |
2019-06 | 953.00 | 482.00 | 471.00 | 439.75 |
2019-07 | 976.00 | 514.00 | 462.00 | 439.75 |
2019-08 | 922.00 | 469.00 | 453.00 | 439.75 |
2019-09 | 863.00 | 498.00 | 365.00 | 439.75 |
2019-10 | 870.00 | 513.00 | 357.00 | 439.75 |
2019-11 | 843.00 | 539.00 | 304.00 | 439.75 |
2019-12 | 825.00 | 565.00 | 260.00 | 439.75 |
Now here the problem is i have to calculate the sum of sales1-sales2 for every year individually and divide it with 12 to show average / year
ex: sum of sales1-sales2 from 2018-01 to 2018-12 is 7836 and divide it with 12 and show value 653 in Average/year in a table and graph visual.
sum of sales1-sales2 from 2019-01 to 2019-12 is 5277 and divide it with 12 and show value 439.75in Average/year in a table and graph visual.
Please help me in sortout this issue.
I have created a measure
Solved! Go to Solution.
Hi, @krrish116
You can try calculated columns as below:
Year = YEAR('Table'[Date])
Average / Year = CALCULATE(SUM('Table'[Sales1 - Sales2]),ALLEXCEPT('Table','Table'[Year]))/12
Please check my sample file for more details.
Best Regards,
Community Support Team _ Eason
Hi, @krrish116
You can try calculated columns as below:
Year = YEAR('Table'[Date])
Average / Year = CALCULATE(SUM('Table'[Sales1 - Sales2]),ALLEXCEPT('Table','Table'[Year]))/12
Please check my sample file for more details.
Best Regards,
Community Support Team _ Eason
@krrish116 , Try a new measure like
CALCULATE(AverageX(values('Date'[Month Year]) ,calculate(sum(Table[Sales1])-SUm(Table[Sales2]))), DATESYTD('Date'[Date]))
It is not working ..
I have to show same average value for all 12 months ..
ex: sum of sales1-sales2 from 2018-01 to 2018-12 is 7836 and divide it with 12 and show value 653 in Average/year in a table and graph visual.
sum of sales1-sales2 from 2019-01 to 2019-12 is 5277 and divide it with 12 and show value 439.75in Average/year in a table and graph visual.
@krrish116 , Rolling 12
example
Rolling 12 = calculate(AverageX(Values('Date'[MONTH Year]),CALCULATE(sum(Sales[Sales Amount])),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,Month ))
Rolling 12 = divide( CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-12,MONTH)) ,
CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH), filter(Sales,not(isblank(sum(Sales[Sales]))))))
User | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |