dax formula

[Bakhtawar]

I have this formula in qliksense

(
RangeMax(0,RangeMin(( POLICY_END_DATE-POLICY_EFFECTIVE_DATE+ 1),(Date(vPE_Date) - POLICY_EFFECTIVE_DATE +1)))
)
/
(POLICY_END_DATE-POLICY_EFFECTIVE_DATE+ 1)

i am trying to rewrite this dax formula

vEarned_Factor = (CALCULATE(Max(0,min('Members'[POLICY_END_DATE]) - min('Members'[POLICY_EFFECTIVE_DATE])),(ENDOFQUARTER - 'Members'[POLICY_EFFECTIVE_DATE]+1))) /'Members'[POLICY_END_DATE] -  'Members'[POLICY_EFFECTIVE_DATE

But this some errors

any idea how to rewrite this ?

[amitchandak]

Can you explain the logic you want?

[Bakhtawar]

this part is related to earned days .. like how much days we earned .

RangeMax(0,RangeMin(( POLICY_END_DATE-POLICY_EFFECTIVE_DATE+ 1),(Date(vPE_Date) - POLICY_EFFECTIVE_DATE +1

and this part is realted to total days

POLICY_END_DATE-POLICY_EFFECTIVE_DATE+ 1

..  @amitchandak 

[Bakhtawar]

@amitchandak @Icey  any help please

[amitchandak]

datediff is the function can you diff between two dates. Are these dates part the same table and same row.  The create a new column. If not. If possible please share a sample pbix file after removing sensitive information.

https://docs.microsoft.com/en-us/dax/datediff-function-dax

Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Bakhtawar]

check files below

[Bakhtawar]

@amitchandak  ok now check files

check files from here

https://filebin.net/pcqilpof7hegf9ab 

[amitchandak]

Based on what I can understand

divide(if(max(_d1) <max(_d2),max(_d1),max(_d2)),max(_d1))
or
divide(if(sum(_d1) <sum(_d2),sum(_d1),sum(_d2)),sum(_d1))

[Bakhtawar]

@amitchandak  I DID NOT UNDERSTAND

[amitchandak]

oh, something was missing

var _d1 =(datediff( POLICY_END_DATE,POLICY_EFFECTIVE_DATE,day)+ 1)
var _d2 = (datediff([vPE_Date].Date,POLICY_EFFECTIVE_DATE) +1)

divide(if(max(_d1) <max(_d2),max(_d1),max(_d2)),max(_d1))

divide(if(sum(_d1) <sum(_d2),sum(_d1),sum(_d2)),sum(_d1))

[Bakhtawar]

these 2 should be one measure .. i dont want to seprate

var _d1 =(datediff( POLICY_END_DATE,POLICY_EFFECTIVE_DATE,day)+ 1)
var _d2 = (datediff([vPE_Date].Date,POLICY_EFFECTIVE_DATE) +1)

and what is this ?

divide(if(max(_d1) <max(_d2),max(_d1),max(_d2)),max(_d1))

divide(if(sum(_d1) <sum(_d2),sum(_d1),sum(_d2)),sum(_d1))

@amitchandak 

[amitchandak · Accepted solution]

 One of them will give you result. That qlik funtion gives min max out of two, Tried that

measure =
var _d1 =(datediff( POLICY_END_DATE,POLICY_EFFECTIVE_DATE,day)+ 1)
var _d2 = (datediff([vPE_Date].Date,POLICY_EFFECTIVE_DATE) +1)
return

divide(if(max(_d1) <max(_d2),max(_d1),max(_d2)),max(_d1))

Or

measure =
var _d1 =(datediff( POLICY_END_DATE,POLICY_EFFECTIVE_DATE,day)+ 1)
var _d2 = (datediff([vPE_Date].Date,POLICY_EFFECTIVE_DATE) +1)
return

divide(if(sum(_d1) <sum(_d2),sum(_d1),sum(_d2)),sum(_d1))