Hide zero values of measures in line graph in future months, but not in previous months

[Anonymous]

Hi there,

I have a line graph with two lines: 1) a measure showing number of Google Analytics (GA) sessions per month for Current Year (CY), 2) a measure showing number of Google Analytics (GA) sessions per month for Previous Year (PY). For some months, there are no values, which causes the graph to show gaps. In order to solve this, I added 0 to the measure that calculates the number of sessions. But now, it also shows zeros for future months, which makes sense, but it is not desired.

GAsessionsCY =

CALCULATE (

    [Visits] + 0;

    'Google Analytics'[Year] = YEAR(TODAY())

)



GAsessionsPY =

CALCULATE (

    [Visits] + 0;

    'Google Analytics'[Year] = YEAR(TODAY()) - 1

)


Visits = SUM('Google Analytics'[Sessions])

I have tried the idea of the first part of the following article, but I think this doesn't work because of the addition of 0 to the measure and this article assumes blanks:

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

So this resulted in the following:

GAsessionsCY =

CALCULATE (

    [Visits];

    CALCULATETABLE('Date';'Date'[DatesWithSessions] = TRUE);

    'Google Analytics'[Year] = YEAR(TODAY())

)

GAsessionsPY =

CALCULATE (

    [Visits];

    CALCULATETABLE('Date';'Date'[DatesWithSessions] = TRUE);

    'Google Analytics'[Year] = YEAR(TODAY()) - 1

)

Or, if I use the Date dimension properly:

GAsessionsCY =

CALCULATE (

    [Visits];

    CALCULATETABLE('Date';'Date'[DatesWithSessions] = TRUE);

    'Date'[Year] = YEAR(TODAY())

)

GAsessionsPY =

CALCULATE (

    [Visits];

    CALCULATETABLE('Date';'Date'[DatesWithSessions] = TRUE);

    'Date'[Year] = YEAR(TODAY()) - 1

)

 I guess what I'm really trying to do, is to get the measures to calculate only for months before the current month. So I tried applying the logic of the CALCULATETABLE to the [Visits] measure, but without any success.

So can anyone help me in this? It's driving me crazy! Thanks.

[dax]

Hi @Anonymous , 

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data )? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

Thanks for your reply. Basically I want the encircled part of the line to disappear, given the measures mentioned in my original post, without zero values disappearing in months in the past:

[Syndicate_Admin]

Hello

I wish you could know that you could solve this problem. I am analyzing graphs that also compare this year with past years and the future months represent them with 0, which is not correct. Thank you

[Anonymous]

I have this same issue when i'm presenting 12 months of forecasted data and am showing comparative actual data by it's side but only have a subset of the months (5 months for example) so the remaining 7 months baseline to zero. I fixed this in excel by creating a fake error of #N/A which nulled the value from the graph but PowerBi doesn't accept the text in a value column, so I don't have that option here. Was a solution for this ever found?

[dax]

Hi @Anonymous , 

You could refer to my sample for details. Two methods: create relationship between calendar table and fact table or create relationship between fact table and table (2), because I don't know your data structure, you could choose one of these.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.