Column chart: cluster/sum dates to quarters and years

[LisanSpiegelaar]

Hi all,

This is my first post to this forum after having read hunderds of threads 🙂

I have got data on (expected) revenue by the date construction is expected start (dd-mm-yyyy):

Estimated start time construction	Revenue
01-01-2019	100.000
10-03-2019	250.000
15-06-2019	500.000
01-07-2019	200.000
01-08-2019	100.000
10-11-2019	350.000
01-12-2019	350.000

I also have a calculated date table:

Date = 
ADDCOLUMNS (
CALENDAR (DATE(year(now())-10,1,1), TODAY()),
"DateAsInteger", FORMAT ( [Date], "DD-MM-YY" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
"MonthNameLong Year", FORMAT ( [Date], "mmmm YYYY" ))

Both of these table have a relationship in my model via the columns 'Estimated start time construction' and 'Date':

In my report, I want to show a column chart with the total revenue by year based on the estimated start time construction, with the ability to drill down to quarters.

So I want my column chart to show these values:

2019		1.850.000
	2019Q1		350.000
	2019Q2		500.000
	2019Q3		300.000
	2019Q4		700.000

If I create a column chart in my report using Estimated start time construction as my x-axis, and Revenue as my values, Power BI gives me this:

Using 'Year' from my Date table doesn't solve it:

I have looked into creating a measure where I sum the revenue within a certain time range, however I do not think that this is a feasible solution as data keeps being added to my data set. So I would continuously have to add measures for the new time periods. 

I am not sure which steps to take and hope that someone can help me along. Thanks in advance and please let me know if more clarification is necessary!

Kind regards,

Lisan

[amitchandak]

@LisanSpiegelaar , based on what I got so far. You should create a date without time first of all

Date = [datetime].date
or
Date = date(year([datetime]),month([datetime]),day([datetime]))

or

DD/MM/YYYY to MM/DD/YYYY
date(year( right(DD__MM__YY[date],4)), month(mid(DD__MM__YY[date],4,2)) ,day(left(DD__MM__YY[date],2)))

Join the date without time with date table and use

[LisanSpiegelaar]

Hi @amitchandak 
Thank you for your reply. I changed the column to date format dd/mm/yyyy but unfortunately this made no difference to the graph in my report. Any further ideas on how to proceed? 
Thanks!