topic Re: Deviation Formula - Dax year wise in DAX Commands and Tips

Deviation Formula - Dax year wise

Anonymous — Wed, 05 Jan 2022 05:58:03 GMT

i have three 4 years data in my table . actuals values for following 4 years
2019,2020,2021,2022

2019 2020 2021 2022

Jan 100 345 567

Feb 200 564 345

Mar 300 645 678

Apr 400 234 456

May 500 656 787

June 200 600 867 443

July 300 700 345 566

i am using actuals value for the above graph . i am trying to find deviation year wise like 2020 -2021 and 2021 -2022

i am using below measure but i am not getting correct value

2020 vs 2021 Measure = CALCULATE(SUM('Current Forecast'[Actuals])-SUM('Current Forecast'[Actuals])/calculate(SUM('Current Forecast'[Actuals]))).

any idea . thanks in advance.

Re: Deviation Formula - Dax year wise

VahidDM — Wed, 05 Jan 2022 06:32:55 GMT

Hi Anonymous

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

Re: Deviation Formula - Dax year wise

amitchandak — Wed, 05 Jan 2022 06:33:39 GMT

Anonymous , Unpivot this data have year on rows

https://radacad.com/pivot-and-unpivot-with-power-bi

then try measure like these and take diff

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Year'),'Year'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Year'),'Date'[Year]=max('Date'[Year])-1))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

Have year table

year = distinct(Table[year])

Re: Deviation Formula - Dax year wise

Anonymous — Wed, 05 Jan 2022 07:05:03 GMT

amitchandak thank you . its working .is there any possible to hidden column header in table ?

Re: Deviation Formula - Dax year wise

amitchandak — Wed, 05 Jan 2022 07:07:49 GMT

Anonymous , I did not get this

is there any possible to hidden column header in table ?

If you trying to hide a column in visual, I doubt that

Re: Deviation Formula - Dax year wise

Anonymous — Wed, 05 Jan 2022 07:25:34 GMT

amitchandak i am trying hide column header name in table . it is possible hide column header name ?

Re: Deviation Formula - Dax year wise

amitchandak — Wed, 05 Jan 2022 07:39:10 GMT

Anonymous , I doubt an option hide column header, What I have done is past is font color same as the background