Taking a visual studio statement and changing it to DAX

[LiamAMurray1975]

I have a visual studio report that requires two fairly critical query statements based on two parameter fields:

1. NEWSTARTDATE=IIf(Parameters!StartDate.Value>Fields!new_arrivaldate2Value.Value,Parameters!StartDate.Value,Fields!new_arrivaldate2Value.Value)

2.

NEWENDDATE=IIf((IsNothing(Fields!new_departuredate2Value.Value) = true)Or(Fields!new_departuredate2Value.Value>Parameters!EndDate.Value),Parameters!EndDate.Value,Fields!new_departuredate2Value.Value)

Essentially these make arrival date 1st of the month if good arrived earlier than this and make departure date end of month for any goods still in stock (allowing for a calculation on storage costs for the month to be performed).

I really want to be able to replicate this in power BI, which I suspect is possible, but I am struggling with the use of parameters and rewriting the two statements in DAX (I have very limited knowledge of power BI).

I would be hugely grateful if someone was able to point me in the right direction. 

[v-luwang-msft]

Hi @LiamAMurray1975 ，

Please provide sanitized sample data that fully covers your issue.

Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Best Regards

Lucien

[LiamAMurray1975]

Hi Lucien,

I am not sure there is very much more data that I can provide than is highlighted above. I have two date fields within Dynamics CRM which reflect when a client stock item arrived and when they departed.

I want to do a monthly calculation of all stock storage costs which will calculate the days between arrival and departure. However I have client stock which will straddle more than a single month and hence I need power BI to adjust the start date to the 1st of the month where the actual arrival date is before this and similalry where stock items do not have an end date (because they are still on site) I need power BI to use the last day of the month for this date.

The way I have done this previously in visual studio is to create two parameter fields which allow users to specify the start date and end date for the report, and then the statements I previously shared will mean visual studio either treats the start date as actual date or (where earlier than the paramater start date) it will use the parameter start date. Similarly where their is no end date the statement above will ensure it uses the parameter end date. I can then do a simple days dif calculation between these two new calculated date fields to show the number of days storage any items has had within the reporting period (i.e. the two dates entered in as parameters). Within visual studio this works without issue, but it would be hugely helpful if I could get Power BI to do the same thing...I am just not sure how to go about doing it...