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
jussiwaisto
Helper I
Helper I

Hitrate from two dates problem

Hi,

 

I have a table (offers to customers) where are two date datas and if i want to count a hitrate from one month (deals made in that month / offers made in that month) I should made a count from two date datas. 

 

1. Offer creation date

2. Deal confirmation date

 

Example:

If I want to count hitrate from february (confirmed dates from febryary / offers made in february) I face to problem: I should use two different date slicers but If i put there data slicer about "Offer date" and slicer about "Confirmation date", the system filters only 1 confirmed offer row. I should use two separate dates which won't filter each other.

 

CustomerOffer dateDeal confirmation date
Cust11.1.2019 
Cust22.1.2019 
Cust33.1.2019 
Cust44.1.20196.2.2019
Cust55.1.2019 
Cust61.2.2019 
Cust72.2.2019 
Cust83.2.2019 
Cust94.2.2019 
Cust105.2.2019 
   
Hitrate in february = 1 / 5 = 0,2 = 20% (cust4 / cust6+7+8+9+10)

 

How do I solve this problem?

 

13 REPLIES 13
iplaygod
Resolver I
Resolver I

Hi

I am not 100% sure exactly what filters you want to apply.

If you want to make a count of all offers made within a particular month, and count all customers where Deal confirmation is within the same month

 

Presuming that you are using a Calendar / Date table

Presuming that "Customer" is in fact a Customer ID....

You can create 2 measures:
Count customers Received Offer =

 

VAR currMinDate = MIN('CalendarTable'[Date])
VAR currMaxDate = MAX('CalendarTable'[Date])
RETURN
CALCULATE( DISTINCTCOUNT( yourTable[Customer]), FILTER(ALL( yourTable), yourTable[Offer Date] >= currMinDate && yourTable[Offer Date] <= currMaxDate ) )

Then you create another almost identical measure but adapt the code so that its working on the Deal confirmation date column instead

 

sorry for any typos cannot test live right now

 

If you DO NOT have a date table I highly encourage you to learn about date tables and get one implemented in your model.

But, you can make it work without it by replacing some code in the measure:

 

Count customers Received Offer =

 

VAR currMinDate = MIN(yourTable[Offer Date])
VAR currMaxDate = MAX(yourTable[Offer Date])
RETURN
CALCULATE( DISTINCTCOUNT( yourTable[Customer]), FILTER(ALL( yourTable), yourTable[Offer Date] >= currMinDate && yourTable[Offer Date] <= currMaxDate ) )

This might get wrong results if your column does not actually contain all the dates of the month.

(which might happen if there are no offers betw 20-30th of the month for example)

this is why you really need a date table to get correct results.

 

 

Thank you very much for your answer! :)))

 

I figured out how to make a date table and I did it (I also made relationships from the Offers -table's "Offer date"-column to Date table's "Date" -column. (Real world table name is 'ekoseer Sopimus' and columns are [Allekirjoituspvm] and [Pvm].) Is this right? (There are some dotted line, but I'm not sure what does that mean.)

 

Screen Shot 2019-09-23 at 9.54.54.png

 

Then I made two measures:

Screen Shot 2019-09-23 at 9.57.34.png Screen Shot 2019-09-23 at 9.57.41.png

 

Then I'm using filters like this:

Screen Shot 2019-09-23 at 9.59.03.png

For some reason this "Tarjouspvm_laskenta" (offer date) is blank and the other one seems to give some data.

 

Is there some problem still in relationships?

 

Thank you! :)))

 

It seems that this measuring get's stucked in offer confirmation date [Allekirjoituspvm]. 

 

I get out only those deals which have some date in the [Allekirjoituspvm] column. If that is empty, it doesnt get in to my list. 

Do I have to make to data tables?

what is the logic you want when the offer confirmation date is empty?

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

The logic is next:

 

If the deal confirmation is empty, it means it's just an offer. If there is date, it means that deal is confirmed.

 

 

Do you mean that you also want to count rows that have a blank value?

The measures were specifically made so that they only count those rows/customer ids where there IS a date value given in the column...

 

but if the rows have a blank value, how do you know which month or date they belong to?

you cant count all the blank rows for november (for example) since there the blank rows dont have any dates to group them by?

I dont think I understand what your data looks like.

 

Ok. let's take it again:

 

The situation was this. Every offer hav the offer date and deals have also deal confirmation date. If there is no deal, the confirmation date is empty. 

 

So I have 1 confirmed deal in february and 5 offers made in february. 

 

CustomerOffer dateDeal confirmation date
Cust11.1.2019 
Cust22.1.2019 
Cust33.1.2019 
Cust44.1.20196.2.2019
Cust55.1.2019 
Cust61.2.2019 
Cust72.2.2019 
Cust83.2.2019 
Cust94.2.2019 
Cust105.2.2019 
   
Hitrate in february = 1 / 5 = 0,2 = 20% (cust4 / cust6+7+8+9+10)

 

 

I think I'm very close to the solution. 

 

I made two tests:

1. I made a relationship from the Allekirjoituspvm to Date and filtering worked well. 

2. Then I deleted that. (for testing)

3. Then I made a relationship from the Pvm to Date but filtering stopped working. So this can be one reason for the empty measure.

 

I also checked the date type from Allekirjoitspvm and Pvm, and both are same date -type. 

Screen Shot 2019-09-23 at 10.50.35.png

 

This is interesting...

Try something like this

IDs = ( 
VAR _Cuur_start = MIN('Date'[Date]) 
VAR _Curr_END = Max('Date'[ Date])
return 
calculate(countdistinct(Sales[ID]), not(isblank(Confirmation)) && Sales[Confirmation Date] >= _Cuur_start && Sales[Confirmation Date] <=  _Curr_END
,CrossFilter(Sales[offer Date],'Date'[Date] ) //in case you do not want to follow offer data 
))
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

Thanks, lets try!

 

Something went wrong in the syntax. (?) Did i wrote it right?

 

Screen Shot 2019-09-23 at 11.04.26.png

Then there is a filter which tells the status of an offer (0 is offer, 1 or bigger is deal). I added that filter to measures.

 

Screen Shot 2019-09-23 at 10.28.00.png

You need to use the userealtion to tell which relation to be used. Example

 

calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _Cuur_start && Sales[Sales Date] <=  _Curr_END,USERELATIONSHIP(Sales[Sales Date],'Date'[Date Filer] )

 

 

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

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.