Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I was wondering how I should build a graph that looks like the following:
I want to make a sum, and display it across all months. I can't do this with the "SUM" or "SUMX" function, because if you would spread it across all months, the summation would break apart.
Anyone got an idea?
Solved! Go to Solution.
First you need to have a seperate date table in order to perfom date calculations...
Step 1 : Create a date table -> Go to Modelling click New Table -> enter Dates = CALENDARAUTO()..Now you have a date table call Dates..-> New Column in this table Month = MONTH(Dates[Date])
Step 2 : Create Relantionship between your yourtable[Dates] ( the new only dates you created ) and Dates[Date] ( the calculated table)
Step 3 : Rewrite your formula to Measure = CALCULATE(
SUM(rprtsolarhistorical[Daily Output]);
FILTER(
ALL(Dates[Dates]);
Dates[Date]) <= MAX(Dates[Date])
)
)
Step 4: add the months field from the new Dates table and the "measure"
Hope this works
Seems like you want a cumulative total? Is that correct?
http://www.daxpatterns.com/cumulative-total/
@Greg_Deckler Exactly!
I tried the suggested formula but it just gives me the same output as when I do a normal "SUM" or "SUMX". My DAX expression seams to be right, no?
Measure = CALCULATE(
SUM(rprtsolarhistorical[Daily Output]);
FILTER(
ALL(rprtsolarhistorical[Timestamps]); rprtsolarhistorical[Timestamps] <= MAX(rprtsolarhistorical[Timestamps])
)
)
Yes you formula seems to be correct
It's possible you join an sample of you're dataset perhaps ?
Regards.
@Haegi Here is a small sample of the columns of interest. Do you know what I'm missing/doing wrong?
Daily Output ... Timestamps Sensoraddress
0 2-7-2015 11:25:00 0x0001
0 2-7-2015 11:25:00 0x0002
100 2-7-2015 11:25:00 0x0003
100 2-7-2015 11:25:00 0x0004
100 2-7-2015 11:25:00 0x0005
0 2-7-2015 11:30:00 0x0001
200 2-7-2015 11:30:00 0x0002
200 2-7-2015 11:30:00 0x0003
200 2-7-2015 11:30:00 0x0004
200 2-7-2015 11:30:00 0x0005
200 2-7-2015 11:35:00 0x0001
Not sure what is not working but a long shot I suppose you have months in x-axis and a relantionship between a date table and the timestamp columnn..
Try convert the timestamp to "Only Dates"..if you haven't since Date/time cannot relate to only dates...
In case you need both time a date you need also a time table and split column to only dates and only time..
edit - I noticed in your formula that you use the timestamp from the same table...You need to have a Date table related to your historytimestamp table...and use these dates in FILTER
I'm not entirely sure what you mean, do you mean I have to make 2 extra columns?
One with the date only, and one with the time only -> like this:
Daily Output ... Timestamps Sensoraddress Times Dates
0 2-7-2015 11:25:00 0x0001 11:25:00 2-7-2015
0 2-7-2015 11:25:00 0x0002 11:25:00 2-7-2015
100 2-7-2015 11:25:00 0x0003 11:25:00 2-7-2015
100 2-7-2015 11:25:00 0x0004 11:25:00 2-7-2015
100 2-7-2015 11:25:00 0x0005 11:25:00 2-7-2015
0 2-7-2015 11:30:00 0x0001 11:30:00 2-7-2015
200 2-7-2015 11:30:00 0x0002 11:30:00 2-7-2015
200 2-7-2015 11:30:00 0x0003 11:30:00 2-7-2015
200 2-7-2015 11:30:00 0x0004 11:30:00 2-7-2015
200 2-7-2015 11:30:00 0x0005 11:30:00 2-7-2015
200 2-7-2015 11:35:00 0x0001 11:35:00 2-7-2015
I tried this but if I use this in my DAX expression this won't work:
Measure = CALCULATE(
SUM(rprtsolarhistorical[Daily Output]);
FILTER(
ALL(rprtsolarhistorical[Dates]);
rprtsolarhistorical[Dates] <= MAX(rprtsolarhistorical[Times])
)
)
What am I doing wrong?
First you need to have a seperate date table in order to perfom date calculations...
Step 1 : Create a date table -> Go to Modelling click New Table -> enter Dates = CALENDARAUTO()..Now you have a date table call Dates..-> New Column in this table Month = MONTH(Dates[Date])
Step 2 : Create Relantionship between your yourtable[Dates] ( the new only dates you created ) and Dates[Date] ( the calculated table)
Step 3 : Rewrite your formula to Measure = CALCULATE(
SUM(rprtsolarhistorical[Daily Output]);
FILTER(
ALL(Dates[Dates]);
Dates[Date]) <= MAX(Dates[Date])
)
)
Step 4: add the months field from the new Dates table and the "measure"
Hope this works
HI @konstantinos looks like you are right on it. I had also used the great answer for cumulative totals at Dax Patterns http://www.daxpatterns.com/cumulative-total/
But I need to have my cumulative running over the same year. Have each year overlaying the others. To do this I figure I need a date column that calcuates what day of the year it is (ignoring the actual year) rather than an actual date
At the moment I am getting this
But want it to look more like this.
any help you could give would be greatly appreciated.
Cheers.
@elliotdixon not sure I understand..Sorry..do you need to compare all years in one graph? like current year - previous etc..or you need a lifetime value..What you would have in axis - months ?
Hi @konstantinos Cheers - I want to show all the years on the same graph. One line for each different year. The axis is our season (just a financial year - 1st July to 30th June)
Cheers.
Hi, I faced the same case.
First i suppose you hae a Date table with column Date (complete), Year, Month etc..
I created new measure column like this
CumulativeYear = CALCULATE( SUM('Aggregate'[Amount]); FILTER(ALL(Dates[Year]); Dates[Year] = MAX(Dates[Year])); FILTER(ALL(Dates[Month]); Dates[Month] <= MAX(Dates[Month])))
I use two filter to specify a particular context: for each year you parse all month.
Hi @Haegi
Thanks for the help. I can create the measure with no errors but cannot then bring it into any table or graph??
Get an error
"MdxScript(Model) (2, 43) Calculation error in measure 'InvoiceDetail'[CumulativeYear]: The function MAX takes an argument that evaluates to numbers or dates and cannot work with values of type String."
My code is.
CumulativeYear = CALCULATE(SUM(InvoiceDetail[DETAIL_KMS_TOCHARGE]), FILTER(ALL(Dates[Year]),Dates[Year] = MAX(Dates[Year])), FILTER(ALL(Dates[MonthName]),Dates[MonthName] <= MAX(Dates[MonthName])))
Same result if I remove out the Month filters and just have one on the years.
Good job!
This error explain that MAX function doesnt work with String value.
I suppose the column 'MonthName' is a string like "January" or "Febuary", we must instead use integer value like 1,2,3,4,5 etc..
for the Month column.
Regards.
@elliotdixon An approach ( maybe not the best one - but I don't work much with fiscal dates ) maybe
YTD Current Fiscal = TOTALYTD(SUM(Sales[Amount);'Dates'[Date];"6-30") - The last one is for specifying the end of year ( Fiscal )
Previous YTD Fiscal = CALCULATE([YTD Current Fiscal];DATEADD('Dates'[Date];-1;YEAR))
2 Years Before YTD = CALCULATE([YTD Current Fiscal];DATEADD('Dates'[Date];-2;YEAR))
This way you can have any fiscal year dates in axis and compare all of them..Also can slice on Years
axis = fiscal year 2015 will show also 2014 & 2013
axis = fiscal year 2013 will show also 2012 & 2011
Hope it works..
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |