cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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.

5 REPLIES 5
dax
Community Support
Community Support

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
Not applicable

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:

 

GAsessions.png

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

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
Community Support
Community Support

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.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors