How to convert the case statement from cognos to power bi ?

[Anonymous]

Hello All,

Can anyone please help me in converting /rewriting the below cognos case statement in to power bi . 

Sum(Sales)   *
case
when [Accounting Date] < [FROM_DATE]
and [Transaction Effective Date] <= [TO_DATE]
and [Expiration Date] >= [FROM_DATE]
then _days_between(least([TO_DATE]+1, _add_days([Expiration Date],1)), greatest([FROM_DATE], _add_days([Transaction Effective Date],1)))
/ _days_between(_add_days([Expiration Date],1),_add_days([Transaction Effective Date],1))

Below is the expression i tried 

DateDiffEX = (DATEDIFF( MIN(Max(FACT_TRANSACTION[Loss Date]),DATEADD(DIM_TRANSACTION[Policy Expiration Date],1,DAY)), MAX(MIN(FACT_TRANSACTION[Loss Date]),DATEADD(DIM_TRANSACTION[Transaction Effective Date],1,DAY)),day))

Please help .

[Anonymous]

Hi  @Anonymous ,

Can you show the expected results in the form of pictures or text? We can better help you solve the problem.

Best Regards,

Liu Yang

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

[lbendlin]

You use a case statement for a single comparison - could have used an if statement instead.  You use a [Loss Date] column in the DAX example - where is that coming from?

Usually you want to formulate the business need in plain text, and then translate it into code.

[Anonymous]

HI @lbendlin 

Below is the sample data of the policy table and we have seperate calendar table which is used as a date dimension.

Policy No	Policy Start Date	Policy Accounting Date	Policy End Date	Premium Amount
abc123	01-01-2018	01-02-2018	01-01-2019	1000
abc123	01-01-2019	01-02-2019	01-01-2020	900
abc123	01-01-2020	01-02-2020	02-01-2021	750
abc124	03-02-2020	03-03-2020	03-02-2021	1500
abc125	09-06-2021	09-07-2021	09-06-2022	1500

We are trying to calculate Earned Premium based on date filter and we are trying to convert the following expression . 

Sum(Written Premium)   *
case
when [Accounting Date] < [FROM_DATE]
and [Policy Start Date] <= [TO_DATE]
and [End Date] >= [FROM_DATE]
then _days_between(least([TO_DATE]+1, _add_days([End Date],1)), greatest([FROM_DATE], _add_days([Start Date],1)))
/ _days_between(_add_days([End Date],1),_add_days([Start Date],1))

Note : Policy Number is not unique 

[FROM_DATE] and [TO_DATE] will be coming from the user selection on date slicer . 

Thanks in advance .

Regards ,

Sandeep