Forum Discussion
Solved
DAX
Hi Community, Help me in the DAX for finding the average of Percentage of a for year 1 and year 2. Sample Data for Year 1 and Year 2 Average I am in need Thanks in Advance, ...
I suggest you to create a datetime table and link the date in fact table with that one.
I just did the test, so the data is not completed.
result = VAR Aamount=CALCULATE(sum(Sheet6[amount]),'Sheet6'[column1]="a") VAR totalamount=SUM(Sheet6[amount]) VAR Aamountper= DIVIDE(Aamount,totalamount) VAR nextyearAamount=CALCULATE(sum(Sheet6[amount]),'Sheet6'[column1]="a",DATEADD('Sheet7'[date],1,YEAR)) VAR nextyeartotalamount=calculate(SUM(Sheet6[amount]),DATEADD('Sheet7'[date],1,YEAR)) VAR nextyearAamountperv=DIVIDE(nextyearAamount,nextyeartotalamount) return DIVIDE(Aamountper+nextyearAamountperv,2)Then you can get the result. You need to choose at least one year. In my solution, you need to choose 2017 because I use DAX to calculate the amount in 2018. If you want to choose 2018, then you need to change the DAX.
1
I suggest you to create a datetime table and link the date in fact table with that one.
I just did the test, so the data is not completed.
result =
VAR Aamount=CALCULATE(sum(Sheet6[amount]),'Sheet6'[column1]="a")
VAR totalamount=SUM(Sheet6[amount])
VAR Aamountper= DIVIDE(Aamount,totalamount)
VAR nextyearAamount=CALCULATE(sum(Sheet6[amount]),'Sheet6'[column1]="a",DATEADD('Sheet7'[date],1,YEAR))
VAR nextyeartotalamount=calculate(SUM(Sheet6[amount]),DATEADD('Sheet7'[date],1,YEAR))
VAR nextyearAamountperv=DIVIDE(nextyearAamount,nextyeartotalamount)
return
DIVIDE(Aamountper+nextyearAamountperv,2)Then you can get the result. You need to choose at least one year. In my solution, you need to choose 2017 because I use DAX to calculate the amount in 2018. If you want to choose 2018, then you need to change the DAX.
1