Count occurrence of date in line chart

[kaka]

I have the following table with five rows: 

I am creating a line chart to show the occurence of the dates in the table above in the last ten days.

In order to accomplish this, I can't just simply take the date column and use in my line chart as the value. Doing so will only display the dates in the table and not the last ten days.

To remedy this issue,  I was able to create a separate table which contains last ten days.




And then am using this table's column in my line chart as below: 


The problem of doing it this way is  that I am not able to count the occurence of the dates in this line chart. How can I accomplish this? 

Here's te line chart: 



Notice how the green line consistently shows count of date as 5 regardless of the date.  Instead of this, it should show me a count of 3 for 26th, a count of 2 for 27th, and a cont of 1 for 30th, leaving the rest of the dates blank. 

[DoubleJ]

You can try adding a column to your table that indicates whether the date falls within the last 10 days:

IsWithinLast10Days = 
IF(
    DATEADD('Table'[date].[Date],10,DAY) >= TODAY(),
    TRUE,
    FALSE) 

You can then use this column to filter your visual.

Use the calculated column to filter your visual or report

Hope this helps!

JJ

[kaka]

@DoubleJ, this function will not work on a column with duplicate dates. It gives me the following error: 

A date column containing duplicate dates was specified in the call to function 'DATEADD'. This is not supported.The current operation was cancelled because another operation in the transaction failed. 

Also, I don't think it will solve the problem of displaying the last ten days. it will only display the dates in the table. 

[DoubleJ]

@kaka, I assumend you have a calendar table with unique date entries

You can easily create a new calendar table with the "New Table" function and the CALENDAR() function

Calendar = CALENDAR(DATE(2017,1,1),DATE(2017,12,31))

You need to link the calendar table to your fact table.

Now you can change the masures formula to:

IsWithinLast10Days = 
IF(
    DATEADD('Calendar'[Date].[Date],10,DAY) >= TODAY() && 'Calendar'[Date] <= TODAY(),
    TRUE,
    FALSE) 

does this help?

[kaka]

That is fine. But how do I count the occurence of the dates? 

@DoubleJ, there are two issues here. One, displaynig the last ten day. and two, counting the occurence of dates. So, yes, the calendar table can be used to show the last ten days. But my original table has the dates that I want to count. And I am trying to figure out how to do that. 

[DoubleJ]

Ok, let's assume you have this fact table with dates:

multiple occurances for March 30

You can create a line chart using 

• the date from the calendar table for the axis
• the date from the fact table for the values
• the column "IsWithinLast10Days" as visual filter

Does that work?

[kaka]

@DoubleJ, that is exactly what I want to accomplish. 

May I know where did you crea the IsWithinlasttenDats measure? isit under the fact tabel or under the calendar table? 

Mine looks like this right now: 

[kaka]

Also, anothe rproblem is that the fact table should not contain all the dates. For instance, in my scenario, I don't have any April dates yet i would like to display them in the axis. You were able to do that because you have the April dates in the Fact Table. How can I display the last ten days and count their occrence in the fact table whether they are present in the fact tabel or not. Are you getting my point? 

[DoubleJ]

Your formula looks incomplete... what is CA supposed to be? It does not matter where you define your measure.

If you don't have values for all dates i would recommend to use a bar chart. Like this gaps are better visible. 

[kaka]

@DoubleJ, may I see how you connected the factbale with the date table and where did u create the measure? 

[kaka]

@DoubleJ, My apologies. This is what am getting when i define the measure. 

[kaka]

@DoubleJ, My apologies. This is what am getting when i define the measure. 

[kaka]

Also, anothe rproblem is that the fact table should not contain all the dates. For instance, in my scenario, I don't have any April dates yet i would like to display them in the axis. You were able to do that because you have the April dates in the Fact Table. How can I display the last ten days and count their occrence in the fact table whether they are present in the fact tabel or not. Are you getting my point?