excel formula - DAX Ranking events year over year -

-

i have table of data has format similar following:

eventid | event date --------------------    1    | 1/1/2014    2    | 2/8/2014    3    | 10/1/2014    4    | 2/5/2014    5    | 4/1/2014    6    | 9/1/2014

what trying create dax formula rank each event in order happened year. want end this. way can compare events year on year events don't happen on regular time schedule.

event date | year | rank ------------------------ 1/1/2014   | 2014 |  1 2/8/2014   | 2014 |  2 10/1/2014  | 2014 |  3 2/5/2015   | 2015 |  1 4/1/2015   | 2015 |  2 9/1/2015   | 2015 |  3

i have tried creating formula give me day number of year:

day of year =(yearfrac(concatenate("jan 1 ", year([event date])),[event date])*360)+1

then using rankx on table, cant seem proper result. perhaps not understanding use of rankx or going right way.

=rankx(filter(event,earlier(event[event year])=event[event year]),event[day of year])

or

=rankx(all(event[event year]),[day of year],,1,dense)

any ideas appreciated!

thanks in advance!

create following measures:

[year]:=year(lastdate(event[event date]))

and

[rank]:=rankx(filter(all(event),[year]=year(max(event[event date]))),firstdate(event[event date]),,1,dense)

and result get:

enter image description here

note: dates in uk format , suspect yours in format, rankings not appear tally example, work!


Comments

Post a Comment

Popular posts from this blog

Android : Making Listview full screen -

-
<linearlayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" tools:context=".mainactivity"> <edittext android:layout_margintop="5dp" android:layout_width="match_parent" android:layout_height="50dp" android:background="@drawable/location_edittext" android:id="@+id/locationeditext" android:textsize="15sp" android:textcolor="#999" android:paddingleft="15dp" /> <com.example.ravi_gupta.slider.viewpagercustomduration android:layout_width="wrap_content" android:layout_height="0dp" android:id="@+id/viewpager" android:scrollbarstyle="outsideoverlay" android:layout_weight="0.5...
Read more

javascript - Parse JSON from the body of the POST -

-
i'm using cameratag.com record video on website. service can send json response server can save data database. don't know how proceed here: the documentation states please note json repsonse passed in body of post , not form parameters. here example of how parse response in ruby: data = json.parse(request.raw_post) video_uuid = data["uuid"] but don't know how data on javascript. i'm not experienced in js please bare me :) post sample: { "uuid": "00012710-4b65-0131-ffe6-22000a499ea4", "camera_uuid": "98373a20-79ee-0130-3e42-1231390fcc11", "created_at": "2013-12-20t05:25:02.000z", "percent_complete": 100, "short_code": "kktb4q", "metadata": { }, "state": "published", "recorded_from": "http://singwho.com", "publish_type": null, "formats": [ { "name...
Read more

javascript - How to Hide Date Menu from Datepicker in yii2 -

-
basically in datetimepicker widget date choose first time, how can hide date option widget , filter according time. tried not working want hide remove button [ 'attribute'=>'time', 'label'=>'time', 'format'=>'raw', 'value'=>function($data){ return date('h:i:s',strtotime($data->time)); }, 'filter'=> datetimepicker::widget([ 'model' => $searchmodel, 'attribute' => 'time', 'clientoptions' => [ 'autoclose' => true, 'format' => 'hh:ii', 'showon' =...
Read more