Date Slicer - Two date columns correlated

[Anonymous]

Hi, folks!

I'm having a tought time trying to solve this one.

I have some products the have a creaetion date and time info in a column and a deactivation date and time in another.

All my dashboard is based on the creation time column, as use this reference on the slicer.

My client now wants to know how many products were deactivated in a time window, but as I have the creation date and time on the slicer, it returns a wrong answer to that. I know that i have to correlate the two columns, but everything I tried didn't work.

Does anybody could point me a way?

Thanks in advance!

[Greg_Deckler]

@Anonymous - Difficult to say exactly given the information provided. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

But, seems like the issue might be dealing with date/time intervals. If that is the case, 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[Anonymous]

Hi, Greg!

I have the "created" column, that gives me the date and time of creation of a product and I have "status_desativada", that gives me the date and time of deactivation of a product.

As I said I use the created column on my slicer and all the data is related mainly by it. But When I filter by the slicer it gives me the wrong amount of deactivated products. If you count how many products were deactivated on July, there are 16, but when I filter by the slicer, it gives only one, because only one product was created in July and deactivated in July.

I tried creating a metric using ALLSELECTED, but couldn't make it work.

Hope that with the additional info I could make myself more clear 😃

Thanks for the attention.

[v-alq-msft]

Hi, @Anonymous 

I wonder which slicers you are using. You may try the following measure to see if it helps.

Measure = 
CALCULATE(
   
 DISTINCTCOUNT('Table'[moderavel_ptr_id]),
 FILTER(
      ALLSELECTED('Table'),
      MONTH('Table'[created])=7&&
      'Table'[status]="Desativada"
 )
)

If I misunderstand your thoughts, please show us with some sample data and expected result with OneDrive for business. Do mask sensitive data before uploading. Thanks.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hey, @v-alq-msft ! Thanks for the response!

Here is the link for two sample files (xlsx DB and pbix) to try to make myself clear about what I'm thinking. Hope it does the trick and you're able to help me, cause I'm loosing my sleep over this!

https://gofile.io/d/ayTYi6

Thanks in advance!