Forum Discussion

rqh's avatar
rqh
Frequent Visitor
2 years ago
Solved

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

 

Need Help
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    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.

5 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    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's avatar
    Daniel29195
    Community Champion
    2 years ago

    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's avatar
    gmsamborn
    Super User
    2 years ago

    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

     

  • rqh's avatar
    rqh
    Frequent Visitor
    2 years ago

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

  • garreola's avatar
    garreola
    Regular Visitor
    1 year ago

    Hello I have the problem that I need to show the Current Year unitl it now i.e. February

    However I need to show Prev Year with all months

     

    Cantidad Comprada ThisYEARSelected =
     
    IF( ISBLANK( [Cantidad Comprada] ) ,
        BLANK() ,
        CALCULATE( [Cantidad Comprada] ,
            DATEADD( addCalendarioComprasCat[FECHA_COMPRA] , 0 , YEAR ) )
    )
     
     
    Cantidad Comprada LastYEARSelected =
     
    IF( ISBLANK( [Cantidad Comprada] ) ,
        BLANK() ,
        CALCULATE( [Cantidad Comprada] ,
            DATEADD( addCalendarioComprasCat[FECHA_COMPRA] , -1 , YEAR ) )
    )
     
    I use these formaulas because I'm using a filter selected to show the year the user wans to show
     

     

    Thank you