Show last 6 months in X axis

[gcrespo]

Hi Community,

I'm trying to show just last 6 months of data in a "Line and Stacked Column Bar" visual. The problem is I am not able to show just those 6 months:

If I pick "show items with no data" (some months the result is 0), the visuals shows the full time series:

Data is being read from a table in the following way:

Date | Name | Description

11/08/2021 | Name 1 | Description 1

19/08/2023 | Name 2 | Description 2

20/09/2023 | Name 3 | Description 3

25/09/2023 | Name 4 | Description 4

...

The measure I'm using is as follows:

ProPerMonth =
CALCULATE (
    DISTINCTCOUNT ( 'modifications'[implementation_id] ),
    CONTAINSSTRING ( 'modifications'[description], "Desplegado en PRO" )
)

It includes a filter for the description column.

Could you please provide some help?

Regards,

Gabriel Crespo

[jdbuchanan71 · Accepted solution]

That looks like your 'modifications'[created_at] column has a time on it as well.  In order to link to a date table it has to be just the date, no time component.

You can add a date only calculated column to your modifications table like this.

created_at_date = DATEVALUE('modifications'[created_at])

[gcrespo]

You nailed it! That was the problem 🙂 Thank you for your help!! 

[jdbuchanan71]

That is strange.  There must be some other filter from somewhere else in the model that is affecting the measure.  If you make a new blank page and add the date from the dates table and the measure, does it work there?

[gcrespo]

Hi,

It looks like powerbi doesn't like when I create a date dimension and link it to my table. Apparently, the measure is not using any dates (Check the screenshot, the value appears with a date BLANK).

I have no idea why this is happening. I'm probably doing something wrong when defining the relationship between the tables... (check screenshot)

Any ideas?

Regards,

Gabriel Crespo

[jdbuchanan71]

If you remove the relative date filter, does the measure return results for the most recent months?

[gcrespo]

Hi,

The measure doesn't work, with or without the filter. It only gets back working properly if I remove this new date table and get to use the original one.

Regards,

Gabriel Crespo

[jdbuchanan71]

Turn off the bi-directional filtering from the Date table to the Modifications table.  The filters only need to flow in 1 direction and that may be throwing your model off.

[gcrespo]

Hi again,

I just did it and nothing changed. 😞

[jdbuchanan71]

You can add +0 to the end of your measure to force it to show when there is no data.

ProPerMonth =
CALCULATE (
    DISTINCTCOUNT ( 'modifications'[implementation_id] ),
    CONTAINSSTRING ( 'modifications'[description], "Desplegado en PRO" )
) + 0

But in order for the relative date filter to work you will need a separate date table linked to your fact table.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables

You link your date table to your fact table then set the last 6 months filter on your date table and it will work:

[gcrespo]

Hi,

Thank you for the information. It sounds good, but I'm afraid I need some more help with that. I can create a new Date table:

I can define it as date table using the column "date" for it:

I can link it to my fact table:

And I can use all that in my visual. The problem now is that the relative filter works, but the measure doesn't...

Any ideas?

Regards,

Gabriel Crespo

[JoeBarry]

Hi @gcrespo 

I presume that there is no data in May and July with the filters you built in the measure?

try 

ProPerMonth =
IF(CALCULATE (
    DISTINCTCOUNT ( 'modifications'[implementation_id] ),
    CONTAINSSTRING ( 'modifications'[description], "Desplegado en PRO" )
)= BLANK(), 0 , 
CALCULATE (
    DISTINCTCOUNT ( 'modifications'[implementation_id] ),
    CONTAINSSTRING ( 'modifications'[description], "Desplegado en PRO" )
)

This will show the missing months, but they will be empty

Thanks

Joe

If this post helps, then please Accept it as the solution

[gcrespo]

Hi JoeBarry,

thank you for the information. I'm afraid that's the same as checking "show items with no data" and is not what I'm looking for. By doing that, I get the full-time series shown in the visual (as in my screenshot, from January 2021 to Today). I'm looking for a way to show just 6 months.

Regards,

Gabriel Crespo

[jdbuchanan71]

It only works if you also add a date table to your model.  You cannot use the date from your fact table, you have to use the linked date table or it will not behave correctly.