Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
lseite
Frequent Visitor

Calculate the max date between two different events in all time and THEN apply date slicer

Hello, 

 

i think my issue is quite simple but i can't seem to find the answer. I'm trying to calculate the max date between two events (no limit of time) and then display it only if max date is conform to dateslicer.

 

to simplify my model ,let's say our customers can buy two types of products, A and B

 

i'm trying to calculate the first date when a customer changed from buying A to buying B, and vice versa. I want to display the result (number of switch A->B and B->A) on a specific Month Year.

In my example, the customer X has bought
product A in 07/2022

product B in 08/2022

dateslicer = 08/2022

 

1/ first, i calculate the first date of buying product A and B :

datepurchaseA = min('Date'[Date],filter('Sales', salesA>0),all('Date'[Date])) = 07/2022

datepurchaseB = min('Date'[Date],filter('Sales', salesB>0),all('Date'[Date])) = 08/2022

i remove the date filter so that i am sure i compare dates even if not included in date slicer 08/2022, or else in this example i will have no data for datepurchaseA

 

2/ i check which switch happened

switch = if(datepurchaseA>datepurchaseB, "switch A->B","switch B->A") = "switch A->B"

 

3/ i calculate the date of switch

dateswitch = max(datepurchaseA, datepurchaseB) = 08/2022

 

4/ i need then to calculate the number of switch A->B on selected date (08/2022)
nb_switch_A->B = calculate(nbcustomer,filter(Dim, switch = "switch A->B") = 1

 

here is my problem : whatever the date i select on date slicer, nb_switch_A->B = 1.

It should show 1 only when 08/2022 is selected, actual date of switch.

i tried to add a filter / keepfilter to make the measure "obey" to date slicer but without any luck till now.

 

Anybody knows how i should proceed ?

thanks in advance !

1 ACCEPTED SOLUTION
lseite
Frequent Visitor

Hello everyone, i think i found a solution, maybe rephrasing and explaining my issue helped me understand the full process i needed to implement

 

my issue was that if customer switched from product A to B on july 2022, i want :

July : result = 1

Other months result = 0

and with my previous measure, i had result = 1 whatever the month.

 

i corrected my measure :

nb_switch_A->B =
var mindate = min( 'Dim Date'[Date] )
var maxdate = max( 'Dim Date'[Date] )
return
calculate(
nbcustomer,
filter(Dim, switch = "switch A->B"),
filter(Dim, dateswitch >= mindate && dateswitch <= maxdate))

this way, i count only count switches when they happened on the date selected with the slicer


thanks again Polly and Amitchandak

View solution in original post

5 REPLIES 5
lseite
Frequent Visitor

Hello everyone, i think i found a solution, maybe rephrasing and explaining my issue helped me understand the full process i needed to implement

 

my issue was that if customer switched from product A to B on july 2022, i want :

July : result = 1

Other months result = 0

and with my previous measure, i had result = 1 whatever the month.

 

i corrected my measure :

nb_switch_A->B =
var mindate = min( 'Dim Date'[Date] )
var maxdate = max( 'Dim Date'[Date] )
return
calculate(
nbcustomer,
filter(Dim, switch = "switch A->B"),
filter(Dim, dateswitch >= mindate && dateswitch <= maxdate))

this way, i count only count switches when they happened on the date selected with the slicer


thanks again Polly and Amitchandak

Anonymous
Not applicable

Hi @lseite ,

How about creating a measure .

new measure =

var _max = maxx(filter(all(Table), [Customer] = selectedvalue([Customer] ) && [Date] < selectedvalue([Date]) ), [Date])

return

if([Product] = maxx(filter( all(table0,[Customer] = selectedvalue([Customer] ) && [Date]  =_max), [Product]), "Same". "different")

 

If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

 

Best Regards

Community Support Team _ Polly

 

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

Hello Polly, 

thank you for spending time on my issue !

I must admit i dont understand what the measure you're suggesting is supposed to provide. Does it check, for each customer, if date of purchase is equal to slicer selection ?

 

i'm sorry i cannot provide any pbixfile as i have a ssas connexion

 

if i try to re-phrase my problem :

i want to calculate how many customers have :

1/ bought product A in the past (anytime)

2/ bought for the first time product B on period selected by slicer

 

my issue is that i use ALL fonction to calculate first date of purchasing product A and first date of purchasing porduct B but then i want to filter only on customers who changed between the two on period selected by slicer

 

don't know it it's clearer...

amitchandak
Super User
Super User

@lseite , Assume you table has customer, date and product , you can get every time a product switch happen in a new column like 

 

new column =

var _max = maxx(filter(Table, [Customer] = earlier([Customer] ) && [Date] < earlier([Date]) ), [Date])

return

if([Product] = maxx(filter( [Customer] = earlier([Customer] ) && [Date]  =_max), [Product]), "Same". "different")

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hello Amitchandak, 

 

thank you for your time, 

i'm sorry i forgot to mention i use ssas live connection and hence i cannot create any new column...

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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