cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Is there a way to get date slicer from date to a parameter ?

My requriment is this, I have two tables, one contains mobile phone numbers and other has usage details for those numbers.

In the report there is a date slicer wich we can select number of months that we want to see inactive mobile numbers.

 

The trick is, if a mobile number is active in Januery and inactive in other months, as the current month is Augest if we select 8 months the perticuler number should not come to the report as it is active in that selected period. But if we select 6 months from the slicer that number should apper as inactive witthin that period.  

 

What I thought is to create two tables for both active and inactive numbers based on the dates. And in the report, filter the active numbers out based on the slicer date range. 

 

What are the ways to achive this in Power BI ?

 

Below are the samples of table details as I see some of you got mis understood the senario.

 

Active_phonenumbers

TelephoneInvoice_dateUsage_ID
1234510/Jan/20201
5432110/Feb/20201
5432110/Mar/20202
5432110/Jan/20201

 

Inctive_phonenumbers

TelephoneInvoice_dateUsage_ID
5432110/Jan/20202
1234510/Feb/20201
1234510/Mar/20201
1234510/Jan/20201

 

Now if I select 

 

I should get 12345 as Inactive. That's not a problem for me.

slicer.jpg

 

The problem is if I select 8 months as below 

slicer2.jpg

 

I should NOT get 12345 as inactive because the number has an active record in Jan 2020. (within the slicer date range).

8 REPLIES 8
Super User III
Super User III

Assuming you have two tables;
DimPhoneNumber
FactUsageDetails
and that these are related on the column [Number]

You could create a MEASURE that counts inactive phone numbers:
Total Inactive Numbers = COUNTROWS(FILTER(DimPhoneNumber, COUNT(FactUsageDetails[Number])<1)

This should update as you increase or decrease number of months selected from the DimDate table.

 


______________


Check out my Data Stories Gallery Christmas Report: Are you on Santa's Naughty or Nice List?

______________


Has this post solved your problem? Please mark it as a 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.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Community Champion
Community Champion

Hi @shihanhh 

 

I would crea a measure for this purpose

Assuming as no detail about tabel structure is provided. Below are the columns names and data it will store.

Mobile - has mobile number

Date  - is date of active inactive

status - stores active or inactive

 

Active_or_inactive = CALCULATE(MIN(table[status]), allexcept(table,table[date],table[mobile]))


Hope it resolves your issue? Please consider accepting it as the solution to help the other members find it more quickly.


Appreciate your Kudos, Press the thumbs up button!!👍


Regards,
Pranit


Thank for the reply @pranit828 and @AllisonKennedy . I have updated the the question for more clarification.

@shihanhh
Thanks for the new info. I am more confused now, lol.

Can you please clarify if the inactive and active tables you have posted are from the raw data source or if you have created them yourself?

Looking at these two tables, it looks like 12345 is in both active and inactive for Jan 2020 - is that a typo from your sample data or do we need to tell Power BI to assume that if it's in the active table then to remove it from inactive?

This problem would be much simpler if they are in 1 table, so if you have changed the raw data at all, let us know, otherwise you can create a custom MEASURE and add that as a filter on the Inactive table:

Active Numbers = 0 + COUNTROWS(Active_phonenumbers)

Add this Active Numbers measure as a filter on the table visual showing Inactive numbers, then filter for Active Numbers = 0

On a side note, what does Usage_Id relate to?

 


______________


Check out my Data Stories Gallery Christmas Report: Are you on Santa's Naughty or Nice List?

______________


Has this post solved your problem? Please mark it as a 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.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Hi @AllisonKennedy The number 12345 is active only on Jan the inactive record for Jan is 54321. Which you may confused.

 

I have created these two tables becase there are few other logics to identify as Active and inactive. For example, usage details, subscription to date etc...

 

Furthermore, I have added keys to relate these two tables as Telephone+Year(Invoice_date)+Month(Invoice_date)

Thanks @shihanhh

The bottom row in your inactive table is 10/Jan/2020 for 12345?

I forgot to mention that for my solution above to work, you will need to be using a DimDate and DimNumber tables to relate these two tables, and you will need to use that that DimNumber[Number] column and DimDate[Date] in the visual and slicer. I don't recommend creating a relationship between these two tables directly because they are both Fact tables in essence.
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

 


______________


Check out my Data Stories Gallery Christmas Report: Are you on Santa's Naughty or Nice List?

______________


Has this post solved your problem? Please mark it as a 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.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

@AllisonKennedy Dim Date is already there but not dim phone numbers. I can add that.

Please note that this is just a small part of a big solution which is in production and the data model is not desined by me. Therefore, I cannot make big changes to the data model as it may break other bunch of reports.

The other question you asked I have put 12345 and also one record of 54321 to differentiate. Those two tables records are as it is I entered.

Hi @shihanhh 

I would keep only one table with on more column which says 'Status' and would use the below.

Slicing the table into two for just active/inactive might not be the best of solution.

Active_or_inactive = CALCULATE(MIN(table[status]), allexcept(table,table[date],table[Telephone]))

Hope it resolves your issue? Please consider accepting it as the solution to help the other members find it more quickly.


Appreciate your Kudos, Press the thumbs up button!!👍


Regards,
Pranit


Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors