Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
rqh
Frequent Visitor

Line Chart with Current Year and Prior Year values

Hi everyone!

My line chart has two lines: one for current year sales (CY) and the other for prior year sales (PY). For the most recent year, CY records stop in May. I would like for the chart to not display any months beyond May for the PY. I have a slicer for the years.

Any suggestions? 🙂

 

Screenshot 2024-01-19 143802.pngScreenshot 2024-01-19 150345.pngScreenshot 2024-01-19 151023.pngScreenshot 2024-01-19 151048.pngScreenshot 2024-01-19 151059.png

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Try this measure

PY net sales new = if([Net sales]=blank(),blank(),[net sales])

If this does not work, then share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
rqh
Frequent Visitor

Hi everyone! Thank you for all the suggestions, I went ahead with @Ashish_Mathur's solution because it was the most straightforward approach.

Ashish_Mathur
Super User
Super User

Hi,

Try this measure

PY net sales new = if([Net sales]=blank(),blank(),[net sales])

If this does not work, then share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Daniel29195
Super User
Super User

@rqh 

create a calcuated column inside dimdate table that will return 1 or 0 base on this condition : 

new column =  

var max_date = max(table_name[date]) -- table_name =  the fact table on which your calculations are based,  such -- as net sales, gross sales ,...

 

RETURN 

switch(

true(),

dimdate[date] <=max_date , 1, 0 )

 

 

after you finish this column, 

put it in the filter pane on page level , and choose advanced filtering :  

is = 1 

 

this should fix your problem .

 

 

let me know if this would help .

 

best regards 

 

gmsamborn
Super User
Super User

Hi @rqh 

 

You could use a measure like this to filter your visual.

_Include = 
VAR _EndOfSales =
    MAXX(
        ALL( 'Sales'[Date] ),
        'Sales'[Date]
    )
VAR _EndOfMonth = EOMONTH( _EndOfSales, 0 )
VAR _Logic =
    IF(
        SELECTEDVALUE( 'Date'[Year] ) = BLANK(),
        1,
        IF(
            MAX( 'Date'[Date] ) > _EndOfMonth,
            0,
            1
        )
    )
RETURN
    _Logic

 

Let me know if you have any questions.

 

Filter PY visual.pbix

 



Proud to be a Super User!

daxformatter.com makes life EASIER!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!