Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am writing to ask something which I believe it is simple but for me as new to Power BI I am struggling.
Basically I have a set of records and all of them have a date. I want to SUM a column based on the month and year.
Any suggestions ?
Solved! Go to Solution.
@iioannou,
Create a column using DAX below.
Column = CALCULATE(SUM(Table1[Amount]),ALLEXCEPT(Table1,Table1[Date]))
Regards,
Lydia
Hello,
just sum the value and add month and year to your PivotTable. DAX does the magic. Measure:=Sum([Value]), no calculated column.
You can get MonthNumber:=Month([Date]) or MonthName:=Format([Date],"mmm") and Year:=Year([date] as calculated columns.
Hello,
Not sure I follow. Basically what I want to do is the following
Column = SUM(COLUMN) WHERE MONTH = MONTH AND YEAR = YEAR
For example - if I have the following data
Date | Amount |
25-01-2017 | 100 |
26-01-2018 | 120 |
27-01-2018 | 100 |
28-01-2018 | 80 |
The outcome I want is a new column on the right that will be Month SUM
e.g
- for January 2017 it will be 100 only
- for January 2018 it will be 300
Please let me know if it is now clear.
Hello,
yes absolutely clear. Please add calculated columns for Month and Year, Month Number is optional but I recommend to add this too.
Then create your Measur as Sum of the Value Column, and put Year and Month in PivotTable rows and your measure in the value field.
Thanks. I have created the Year and Month columns. Now how can I create another column or measure which will be the SUM of all records of that month and year ?
Hello,
you create a Measure for the sum of all months and years and if you group your PivotTable by month and year it will give you the result as requested.
I understood that - but I need that to be in a colulmn...
That is why I asked If could do something like this in Power BI
column = SUM( column for sum) where same month and same year from all records...
@iioannou,
Create a column using DAX below.
Column = CALCULATE(SUM(Table1[Amount]),ALLEXCEPT(Table1,Table1[Date]))
Regards,
Lydia
Thank you - that was what I was looking for
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |