Date grouping for StartOfMonth / StartOfQuarter / StartOfYear

[ruperts]

Hi,

I've pulled in some data from Dynamics CRM and am looking to do quarterly/annual reporting.

I've converted the Close date of an opportuntity by using

= Date.StartOfMonth([ActualCloseDate])

This seems to return a string as when it's ordered in a PoweBI chart as the order goes:

01/01/2013

01/01/2014

01/01/2015

01/02/2013

01/02/2014

Any ideas?

Phase 2 is then to bring back data just for the current month - but I'll worry about that later.

Many thanks for any help.

[ruperts · Accepted solution]

Solved my own problem..In data tools, click on the field, choose the field, choose hte modeling tab, slect data type.

[Rémi]

Hi,

I advice you to load your query without modification, it's better to do something like that into your model (once your data are loaded).

If you just load your data, you have one column [ActualCloseDate] (care abouth that, scheduled date are often better than final date in CRM) then you add the other columns :

Month = FORMAT( [ActualCloseDate] , "mmmm")

number Month = MONTH( [ActualCloseDate] ) (sort the [Month] column by this one)

Year = YEAR( [ActualCloseDate] )

Quarter = STAROFQUARTER( [ActualCloseDate] )

... and more