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 All,
I try to filter a table by year , using DAX. My calculation is like this but it does not work.. Can you help?
Measures = Filter(Sales,Sales[Year]=2016)
What I have is...
Table : Sales
Date Branch Sales Items Year
1 Jan 16 Tokyo 100 Chocolate 2016
3 Jan 16 Nagoya 50 Candy 2016
9 Feb 16 Tokyo 30 Biscuits 2016
2 May 17 Tokyo 20 Gum 2017
24 Dec 17 Osaka 40 Biscuits 2017
5 Jan 18 Nagoya 20 Chocolate 2018
1 Apr 18 Tokyo 30 Candy 2018
What I want to is... sales by year
Table : 2016
Date Branch Sales Items Year
1 Jan 16 Tokyo 100 Chocolate 2016
3 Jan 16 Nagoya 50 Candy 2016
9 Feb 16 Tokyo 30 Biscuits 2016
Table: 2017
Date Branch Sales Items Year
2 May 17 Tokyo 20 Gum 2017
24 Dec 17 Osaka 40 Biscuits 2017
Table : 2018
Date Branch Sales Items Year
5 Jan 18 Nagoya 20 Chocolate 2018
1 Apr 18 Tokyo 30 Candy 2018
I initially tried it by using filter in query function but it causes DataFormat.Error:External table is not in the expected format ...
So I can not refresh data any more.
I am thinking if I can do it by using DAX. Appreciate your help.
All the best,
cocomy
Solved! Go to Solution.
Hi cocomy,
To achieve your requirement, you can create three calculate tables by clicking Modeling->New Table, then use DAX formula:
Table_2016 = CALCULATETABLE(Sales, Sales[Year] = 2016)
Table_2017 = CALCULATETABLE(Sales, Sales[Year] = 2017)
Table_2018 = CALCULATETABLE(Sales, Sales[Year] = 2018)
Regards,
Jimmy Tao
Hi cocomy,
To achieve your requirement, you can create three calculate tables by clicking Modeling->New Table, then use DAX formula:
Table_2016 = CALCULATETABLE(Sales, Sales[Year] = 2016)
Table_2017 = CALCULATETABLE(Sales, Sales[Year] = 2017)
Table_2018 = CALCULATETABLE(Sales, Sales[Year] = 2018)
Regards,
Jimmy Tao
Hi Jimmy
Thank you very much for your help. This is what I have been looking for.
All the best,
cocomy
Hi,
Build a table visual and in the visual filter section, drag the Year. Select any year in the Filter section.
Hi
Thank you for your suggestion. I am going to make a graph by year like this. (see attached)
If I filter by vizualization, I can only get one year by one year so I can not compare them at once?
All the best
cocomy
Hi,
This should get you started. Download the file from here.
Hope this helps.
Hi Ashish
Thank you very much for your advice.
Apology, my explnanation was not clear. X is Month and Y is Sales and I would like to compare Year on Year.
All the best,
cocomy
Hi All,
When I duplicate a table in query, it shows DataFormat.Error:external table is not in the expected format and can not refresh.
I have a table of sales record and want to create different tables by filtering by year.
(1) Sales table
Date Branch Sales
2 Jan 16 Tokyo 50
5 May 16 Nagoya 300
1 Jan 17 Tokyo 200
5 Jan 17 Osaka 100
.
.
1 Dec 18 Nagoya 50
(2) I want to divide into 3 tables by filtering table(1) by year
2016 Sales
2017 Sales
2018 Sales
Is there anyway I can do without causing DataFormt.Error?
All the best,
cocomy
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |