Forum Discussion
Separate year/month column
Hello comunity, I have a problem. I need to separate values for whole year 2018 and then values for year 2019 but by months. Is there any DAX solution so I can take exact year like in SQL? Or do you ...
Hi Anonymous
You may add Month column and Year column in query editor first.Then create measures like below.Attached the sample file for your reference.
Salary for Year 2019 = CALCULATE(SUM(Table1[Salary]),Table1[Year]=2019)
AVG for year 2019 = [Salary for Year 2019]/DISTINCTCOUNT(Table1[Month Name])
Regards,
TomMartens wrote:Hey,
maybe you want to refer to this DAX functions:
- https://docs.microsoft.com/en-us/dax/year-function-dax and
- https://docs.microsoft.com/en-us/dax/month-function-dax
or from within Power Query using "Add column" --> "From Date & Time".
If this does not answer your question, please share an example what you want.
Regards,
Tom
I want to create table like this but i cant find a dax solution for this, how to represent Salary for year 2019 by months and then calculate an average for it.
| Salary for Year 2019 | |||||
| Name | Salary for year 2018 | AVG for year 2019 | Jan | Feb | Mar |
| Milan | 200 | 30 | 30 | 30 | 30 |
| Adam | 150 | 58 | 50 | 100 | 25 |
| David | 175 | 50 | 45 | 55 | 50 |
| Dominik | 100 | 67 | 80 | 50 | 70 |
Hi Anonymous
You may add Month column and Year column in query editor first.Then create measures like below.Attached the sample file for your reference.
Salary for Year 2019 = CALCULATE(SUM(Table1[Salary]),Table1[Year]=2019)
AVG for year 2019 = [Salary for Year 2019]/DISTINCTCOUNT(Table1[Month Name])
Regards,