Nested IF DAX

[o59393]

Hi all

I need to have an if formula that returns me a value from one column (Weight in this case) based on the date ranges (start date and end date).

In the example above, on the right table you will see product1 has 3 rows. The weight differs based on the start and end date.

How can I get a dax metric that returns me correct weight for each day? In the left table you will see a bunch of 0.10, instead of this I would like to have the 0.07, 0 or 0.03

I attach the pbix

https://1drv.ms/u/s!ApgeWwGTKtFdhjRSVqf-G5rb4BdX?e=n7kuj4

Thanks all!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[o59393]

Hi @Ashish_Mathur 

You just did a 

Weight New = SUM(test1[Weight]) * 10

How did that take the right dates and why by 10?

Thanks

[Ashish_Mathur]

Hi,

I did not write that measure.  It was already there in your file.  I carried out transformation steps on the second table to create one row for each date.

[o59393]

Haha sorry, you are right.

Hmm, can it be done with a dax instead of power query?

Thanks Ashish!

[Ashish_Mathur]

Why would you need that.  What is the problem with the solution i have shared with you?

[o59393]

No problem at all, I would just like to see how that's done with dax if possible.

Thanks @Ashish_Mathur !

[Ashish_Mathur]

You are welcome.  if my reply helped, please mark it as Answer.

[o59393]

Hi @Ashish_Mathur 

I tested the solution on the pbix but it's not working well, the thing is that I have more than one weight attribute.

The columns are ingredients from a bill of materials.

Maybe the power query is not the best option or i am doing it wrong

Let me send another pbix with more realistic data. 

Thanks. 

[o59393]

Hi @Ashish_Mathur 

I attach my PBIx using (or trying to use) your steps. In the table dashboard, you will see It is repeating each of the days 3 times.

Also I noticed you removed the end and start date from the query, I need those dates to be visible in my dahsboard for people to see it.

Can you help me?

https://1drv.ms/u/s!ApgeWwGTKtFdhjb-8R5LAVb1Amnk

Thanks

[Ashish_Mathur]

I am confused over what you want.  Share a simple dataset and show me the exact result you want.  Please realise that it takes a lot of my time to answer your question so please respect my time and effort and state the question and expected result very clearly.  Review your question time and again before posting - that is the very least i can expect of you.

[o59393]

Hi @Ashish_Mathur 

Apologies for the confusion.

Here is the excel with the desired solution. You will see colors blue, green and yellow that distinguish the values based on the dates.

https://1drv.ms/x/s!ApgeWwGTKtFdhjcOMqI6H9v620Lg?e=sAOzy5

Please do not remove the original columns start and end date with Power Query,

Hope this clarifies.

Regards.

[Ashish_Mathur]

You have shared the expected result.  Now share the original data also in an Excel file.

[o59393]

Hi @Ashish_Mathur 

Here you go

https://1drv.ms/u/s!ApgeWwGTKtFdhjucn0A8Q0gRN9jv?e=mhuaxU

Appreciate your patience and help.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[o59393]

Thank you very much @Ashish_Mathur 

It worked very well.

Apologies once again for not being clear from the start.

Regards. 

[Ashish_Mathur]

You are welcome.  No problem.

[amitchandak]

Please refer to this blog how work with two date, if this can help

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

Appreciate your Kudos.

[o59393]

Hi @amitchandak 

I see you use count x, but in my case is not a count of cases. Is pretty much return a value based on the date ranges.

Appreciate you help!

[amitchandak]

@o59393 , Use this file. It has allocation between dates. You just do a copy no allocation

https://www.dropbox.com/s/yuv64v0cneseghx/value%20Split%20between%20months%20start%20end%20date.pbix...

[o59393]

Hi @amitchandak 

So I should go from:

Measure 2 = CALCULATE(sumx(SUMMARIZE(filter(CROSSJOIN(Sheet1,'Date'),'Date'[Date]>=(Sheet1[Start Date]) && 'Date'[Date]<=(Sheet1[End Date])),Sheet1[Contract],'Date'[Date],"_sum",sum(Sheet1[Daily Value])),[_sum]))

to

Measure 2 = CALCULATE(sumx(SUMMARIZE(filter(CROSSJOIN(Sheet1,'Date'),'Date'[Date]>=(Sheet1[Start Date]) && 'Date'[Date]<=(Sheet1[End Date])),Sheet1[Contract],'Date'[Date], ... 

I get lost in the final part.

Thanks.