cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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

Hi @rneven , 

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.

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

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

Hi @rneven , 

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.

Highlighted
Regular Visitor

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

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

Highlighted
Community Support
Community Support

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

Hi @rneven , 

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.

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors