Line Chart with Current Year and Prior Year values

[rqh]

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? 🙂

[rqh]

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 · Accepted solution]

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.

[Daniel29195]

@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]

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