problems to creating a chart of the monthly volumetric with two labels of type date

[renatofrancavr]

I'm having a problems to creating a chart of the monthly volumetric (Backlog) amount of Closed and Open numbers in the month in Power BI . In my base I have two date fields : Date Open and Date Closed , If I put one of the date fields mentioned above as X-axis , the information in the chart is not correct. Ex: In the X axis I put " Open Date ", in the Values ​​column I put : Open Data and " Date Closed ," but information from " Date Closed " in the chart is incorrect. I think that this way , the tool understands that should only account the closed numbers that entered that month (ex: August / 16). I want to be able to show all that opened and closed in the month, independent of the month that entered (eg May , June , July / 16). Note . If I put the " Date Closed " as X axis the problem is inverted . Can you help me? Thank you.

[v-haibl-msft]

@renatofrancavr

If I understand you correctly, you want to get the open counts and close counts for each month, right?

It seems that you have two columns as below.

You can create a calendar table with following formula.

Calendar = 
CALENDAR ( "1/1/2016", "12/31/2016" )

Then create two columns in this Calendar table with following two formulas.

Open = 
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER ( Table1, Table1[Open Date] = 'Calendar'[Date] )
)

Close = 
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER ( Table1, Table1[Close Date] = 'Calendar'[Date] )
)

Now you should be able to get the desired result as below.

Best Regards,

Herbert

[renatofrancavr]

Hi @v-haibl-msft!!! 

Very Thanks.

I managed to resolved the problem.

Two Questions:

1) The data for November and December 2015, should not be after August 2016 in the chart and yes before January 2016. How I can put in order the months by years?

 2) If I want to see the volume by day , I Can not , because when I leave only the year in the "Axis" it only shows the first day as the picture below .

Regards,

Renato Franca

[v-haibl-msft]

@renatofrancavr

You can create following two columns in the calendar table.

YearMonth = 
FORMAT ( 'Calendar'[Date], "MMM YYYY" )

YearMonth Num = 
YEAR ( 'Calendar'[Date] ) * 100
+ MONTH ( 'Calendar'[Date] )

Select YearMonth column and make it sorted by YearMonth Num column.

Now you should be able to get the desired visual by selecting YearMonth for Axis.

If you want to see the volume by day, you can select Date but not Date Hierarchy in Axis and set X-Axis type to Categorical as below.

Best Regards,

Herbert

[renatofrancavr]

Hi Herbert,

About the months , it's ok . But, the day of volume not. The chart shows the first day of each month , not every day of the month. 

Follow picture below:

Best Regards,

Renato

[v-haibl-msft]

@renatofrancavr

Which table does your Date column from? Does this selected Date column contain other days of the month? Does other days of the months are Open or Close date?

Best Regards,

Herbert

[renatofrancavr]

Hi  @v-haibl-msft.

Thanks for your help. 

I did it!!! \o/ 

As you can see in the figure below, Some dates are not showing up (Ex: 02/09, 04/09...). I would like to show all days of month, even if the value in the day is "0". It's possible?

Best Regards,

Renato Franca

[v-haibl-msft]

@renatofrancavr

You can try to set the X-Axis type to Continuous in Format.

Best Regards,

Herbert

[renatofrancavr · Accepted solution]

Hi @v-haibl-msft

It's ok now.

Very Thanks for your help!!

Regards,

Renato França

[Greg_Deckler]

Let me make sure that I understand your problem. Let's say you have one backlog item:

Opened: 6/19/2016

Closed:   9/2/2016

You would want to see 1 for June, July, August and September and 0 for all other months, correct?

[renatofrancavr]

Hi Smoupre,

I managed to resolved the problem with the solution below of the Herbert_Liu . Thank you anyway .

 But...

I have two more questions:

1) The data for November and December 2015, should not be after August 2016 in the chart and yes before January 2016. How I can put in order the months by years?

 2) If I want to see the volume by day , I Can not , because when I leave only the year in the "Axis" it only shows the first day as the picture below .

Regards,

Renato Franca