Forum Discussion

stribor45's avatar
stribor45
Post Prodigy
2 years ago
Solved

SAMEPERIODLASTYEAR Exclude where no data

I use this function to show the data for the current date selected and the same time a year ago in my table but sometimes I don't have data for the previous period and I wonder whether I can exclude that. As you see in this image I don't have data for Sept 2021 so I would like to exclude that row from the table.

 

 

 

 

 

Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    stribor45 , Try using below method

     

    HasDataLastYear =
    VAR PreviousYear = CALCULATE(MAX('YourTable'[Date]), SAMEPERIODLASTYEAR('YourTable'[Date]))
    RETURN
    IF(
    NOT(ISBLANK(CALCULATE(SUM('YourTable'[YourMeasure]), 'YourTable'[Date] = PreviousYear))),
    1,
    0
    )

     

    Please accept as solution and give kudos if it helps

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi stribor45 ,

     

    Hope all is going well.

     

    To achieve your needs please follow these steps:

     

    1. Create a measure that checks if the previous year's data is empty. This is done to filter out rows that have no data for the previous year.

    measure = ISBLANK([Table])

     

    2. After selecting the visual object, in the "Filter" pane on the right, apply measure as a filter to the current visual object. Set the filter condition to display data when the value of "measure" is FALSE.

     

    If you want to learn more, please refer to the following documents:

    ISBLANK function (DAX) - DAX | Microsoft Learn

     

    If you have any questions or need help, please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    stribor45 , Try using below method

     

    HasDataLastYear =
    VAR PreviousYear = CALCULATE(MAX('YourTable'[Date]), SAMEPERIODLASTYEAR('YourTable'[Date]))
    RETURN
    IF(
    NOT(ISBLANK(CALCULATE(SUM('YourTable'[YourMeasure]), 'YourTable'[Date] = PreviousYear))),
    1,
    0
    )

     

    Please accept as solution and give kudos if it helps

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi stribor45 ,

     

    Hope all is going well.

     

    To achieve your needs please follow these steps:

     

    1. Create a measure that checks if the previous year's data is empty. This is done to filter out rows that have no data for the previous year.

    measure = ISBLANK([Table])

     

    2. After selecting the visual object, in the "Filter" pane on the right, apply measure as a filter to the current visual object. Set the filter condition to display data when the value of "measure" is FALSE.

     

    If you want to learn more, please refer to the following documents:

    ISBLANK function (DAX) - DAX | Microsoft Learn

     

    If you have any questions or need help, please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!