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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.