cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
matokt
Regular Visitor

Filter based on date

Hello, I have a set of data with subscriptions and there I have a column with date & time that shows when the subscription last was used.

 

The date column/data originally showed date and time but I have changed the data type for it to only Date. Theres also empty cells in the date-column, these are to be as inactive.


What I now want to do is to be able to filter the "unused" subscriptions for someone to take action on. 

The requirements are that the date is older than 365 days or the cellvalue is empty/null.

 

Is it a new custom column I need, or maybe a new measurement? I have no idea how to begin this. 

 

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

You could create a Measure similar to the below and filter for "unused" only

Inactive Filter =
VAR _LastDate = MAX('Table'[Date]) + 0
VAR _CutoffDays = 365
VAR _DaysSinceLastUsed = DATEDIFF( _LastDate, TODAY(), DAY)
RETURN
IF(_DaysSinceLastUsed > _CutoffDays, "Unused")

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

7 REPLIES 7
matokt
Regular Visitor

@AllisonKennedy 
Im bumping this question and wondering if theres something else besides a measure to use for a solution.
I have seen that I cannot use the measurement in all the visuals that I'd like.

I want to have a slicer button for yes/no that filters visuals based on the above measure or inactive filter. 

@matokt  Are you using import or direct query? If Import, you can use the same formula as a new column and that can be used as a slicer (if I understand what you're asking correctly?)


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@AllisonKennedy I am getting the data through web/api. 


With this in a new column it aint working. In a measure it is working

Inaktiv2 =
VAR _LastDate = MAX('sub_info'[Senast aktiv]) + 0
VAR _CutoffDays = 365
VAR _DaysSinceLastUsed = DATEDIFF( _LastDate, TODAY(), DAY)
RETURN
IF(_DaysSinceLastUsed > _CutoffDays, "Inaktiv")

@matokt  Since it's a column you won't need the MAX anymore for the date: 

 

Inaktiv2 =
VAR _LastDate = 'sub_info'[Senast aktiv] +0
VAR _CutoffDays = 365
VAR _DaysSinceLastUsed = DATEDIFF_LastDateTODAY(), DAY)
RETURN
IF(_DaysSinceLastUsed > _CutoffDays"Inaktiv")

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

AllisonKennedy
Super User
Super User

You could create a Measure similar to the below and filter for "unused" only

Inactive Filter =
VAR _LastDate = MAX('Table'[Date]) + 0
VAR _CutoffDays = 365
VAR _DaysSinceLastUsed = DATEDIFF( _LastDate, TODAY(), DAY)
RETURN
IF(_DaysSinceLastUsed > _CutoffDays, "Unused")

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@AllisonKennedy
I think that almost works actually. But are measurements not used on side filters? So that all lists on the report page are automatically filtered by the measurement. 

Maybe if I explain more about what I want to do.
I have thousands of mobile plans/subscriptions and from my operatator I can get the date it was last used.
I want to be able to in a whole report page me able to add this measurement or whatever is needed to only show the ones that have nothing filled in the date last used column or havent been used in a year or older.

I solved it by creating a new column with the measurement. I learned something new.

Thanks @AllisonKennedy !

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors