Help With Formula To Calculate Different Fields Based On Time Based Criteria.

[krichmond]

I need to create a DAX formula that looks at a field called "Start Date" and determines if that date is inside or outside of a 90 day window from whatever the current date is. If the "Start Date" is inside of 90 days, then the formula needs to multiply the "Projected Paid Responders" field and the "Projected Average Buy". If the "Start Date" is outside of 90 days, then the formula needs to multiply the "Paid Responders @100%" field and the "Actual Average Buy".

Example of how we made this work in Tableau:

First we created a table with the following formula:

IF DATEDIFF('day',[Start Date],TODAY())<=90 Then TRUE else FALSE
END

Then we used the following formula:

IF [90 Day Date Designator] THEN [Proj Supps] * [Proj Avg Buy Amt]

ELSE [Supps @100%] * [Actl Avg Buy Amt]
END

[Anonymous]

Hello i tried creating a simple sample data of random columns and numebrs and dates:

Here is the PBIX file: https://1drv.ms/u/s!Ag9tIyk2ofNRjlzz4Yk4Jee6a2QW?e=G1MNGq

the sample data table:

Added 90 days verification:

Formula added calculated column :

90 Day Date Designator = IF(DATEDIFF(Krichmond[Date],TODAY(),DAY)<=90,"True","False")

Result:

Formula measure for result:

Measure = IF(MAX(Krichmond[90 Day Date Designator]) = "True",SUM(Krichmond[Amount]) * MAX(Krichmond[Percentage 1]),SUM(Krichmond[Amount]) * MAX(Krichmond[Percentage 2]))

  if this is what you were seeking kindly accept as solution and a kudos is appreciated

[krichmond]

I tried the 90 day designator and it does work! So thank you so much for that! Can you explain why you are using a MAX function for the percentage 1 and percentage 2?

[Anonymous]

its because i sampled the data on excel using randbetween so there were lots of different percentages in the same date i just wanted to use the max just for purpose 

[krichmond]

I just tried the below and got this error: The SUM function only accepts a column reference as an argument.

Any idea what I am doing wrong?

Actual Premium With 90 Day Designator = IF(MAX(mv_perfex[90 Day Date Designator]) = "True",SUM(mv_perfex[projsupps] * SUM(mv_perfex[projavgbuyamt]),SUM(mv_perfex[supps@100%] * SUM(mv_perfex[actlavgbuyamt]))))

[Anonymous]

hello yes you havea  mistake you should always close the paranthesis in funtions so try 
Actual Premium With 90 Day Designator = IF(MAX(mv_perfex[90 Day Date Designator]) = "True",SUM(mv_perfex[projsupps]) * SUM(mv_perfex[projavgbuyamt]),SUM(mv_perfex[supps@100%]) * SUM(mv_perfex[actlavgbuyamt]))

or try

IF(MAX(mv_perfex[90 Day Date Designator]) = "True",SUMX(table,mv_perfex[projsupps]) * mv_perfex[projavgbuyamt],SUMX(table,mv_perfex[supps@100%] * mv_perfex[actlavgbuyamt]))

if you are trying to multiply row by row then sum the result of each row, then use sumx if you want total times total use the first calulation of sum

[krichmond]

This is what I used:

Actual Premium With 90 Day Designator = IF(MAX(mv_perfex[90 Day Date Designator])="True",SUM(mv_perfex[projsupps])*SUM(mv_perfex[projavgbuyamt]),SUM(mv_perfex[supps@100%])*mv_perfex[actlavgbuyamt])

However, the result that is returning is a massive number that is replicated over and over. Is there something else I need to add to the formula to make it where it only calculates numbers from the applicable row?

[Anonymous]

hmm what are the columns of the table?

if you want try 
IF(MAX(mv_perfex[90 Day Date Designator]) = "True",SUMX(table,mv_perfex[projsupps]) * mv_perfex[projavgbuyamt],SUMX(table,mv_perfex[supps@100%] * mv_perfex[actlavgbuyamt]))

sumx multiplies the columns of each row then adds up the result of each row

[krichmond]

This is challenging stuff and I cannot thank you enough for your help. I used this:

Actual Premium With 90 Day Designator = IF(MAX(mv_perfex[90 Day Date Designator])="True",SUMX(mv_perfex[projsupps])*SUM(mv_perfex[projavgbuyamt]),SUMX(mv_perfex[supps@100%])*mv_perfex[actlavgbuyamt])

But I got this error message:

Too few arguments were passed to the SUMX function. The minimum argument count for the function is 2.

[Anonymous]

its ok if youre new to powerbi calculations could be a bit tricky try ths:
 IF(MAX(mv_perfex[90 Day Date Designator])="True",SUMX(yourtable,mv_perfex[projsupps] * (mv_perfex[projavgbuyamt]),SUMX(yourtable,mv_perfex[supps@100%] * mv_perfex[actlavgbuyamt]))

in sumx it is basically SUMX(yourtablename, [measure 1]  (expression: ex: + * / ...)  [measure 2])

[krichmond]

I tried this:

Actual Premium With 90 Day Designator = IF(MAX(mv_perfex[90 Day Date Designator])="True",SUMX(yourtable,mv_perfex[projsupps]*SUM(mv_perfex[projavgbuyamt]),SUMX(yourtable,mv_perfex[supps@100%]*mv_perfex[actlavgbuyamt])))

And got this:

Too many arguments were passed to the SUMX function. The maximum argument count for the function is 2.

[Anonymous]

so let me explain sumx in a simple way:

lets say you want the total of sales for the whole table ,you use sum(table[amount])

then you have a percentage on ever row and you want to find the total of percentage on each row time the total amount

you use sumx(tablename, sum(table[amount]) * table[percentage] )

so lets say this table name is table:

Amount	Percentage
400	0.1
600	0.5
1000	1
2000	0.1

so sum(table[amount] = 4000

now you want the sum of this amount to multiply row by row of the percentage so you use sumx

sumx(table, sum(table[amount]) * table[percentage])

and you get this result in your visual

Amount	Percentage	sum of amount	SUMX
400	0.1	4000	400
600	0.5	4000	2000
1000	1	4000	4000
2000	0.1	4000	400

and your total will display 6800

but if you want to just multiply row by row without summing up anything, use:

sumx(table, table[amount] * table[percentage])

and you get:

Amount	Percentage	SUMX
400	0.1	40
600	0.5	300
1000	1	1000
2000	0.1	200

and the total will display 1540

notice the difference? good luck we are here to help

same goes with MINX and MAXX if you use MAXX in this case, the total will display 4000 for the first table and 1000 for second table while MINX will display 400 in first table and 1000 in second table

i hope this helps to better understand difference between SUM and SUMX or MAX and MAXX or MIN  and MINX

[krichmond]

Thinking about this a different way. Could I simply create a measure field that does this 

Projected Paid Responders x Projected Average Buy and then another field that does this 

Paid Responders @100% x Actual Average Buy and then use the 

Actual Premium With 90 Day Designator = IF(MAX(mv_perfex[90 Day Date Designator])="True" field to just use the applicable field based on the true and false criteria?

[Anonymous]

If you mean by creating first a measure that dies the cslcualtion then create another measure to use the if then yes you can 

[krichmond]

So I did that and it appears to work for items within the 90 day window perfectly. However, when trying to pull the stuff outside of the 90 day window the numbers aren't showing up as expected. Below is a screenshot of a record from 2021 so it is outside of the 90 day window. The 90 Day Designator field shows $915.00 but should show $2,220 which is what Paid Responders @100% x Actual Average Buy equals.

[Anonymous]

thats odd can you please share what you used for the measures

[krichmond]

@Anonymous - Did what I provided on Monday this week help with you determining what is causing the wrong value to populate?

The first attempt I did this: 

Actual Premium With 90 Day Designator = IF(MAX(mv_perfex[90 Day Date Designator])="True",mv_perfex[Projected Paid Responders x Projected Average Buy],mv_perfex[Act_Prem])

I assumed that this would have just pulled the $2,220 that is sitting in the "Actual Premium" field but it is still pulling the $915.

The second attempt I did this: 

Actual Premium With 90 Day Designator = IF(MAX(mv_perfex[90 Day Date Designator])="True",mv_perfex[Projected Paid Responders x Projected Average Buy],[Paid Responders @@100% x Actual Average Buy)

I double checked and confirmed that the value of the "Paid Responders @100%" field is 15 and the value of the "Actual Average Buy" field is $148 which is $2,220.

[Anonymous]

Hello @krichmond sorry for late reply can you please provide me with data example and pictures 

[krichmond]

I tried it two different ways and the $915 is still showing up.

The first attempt I did this: 

Actual Premium With 90 Day Designator = IF(MAX(mv_perfex[90 Day Date Designator])="True",mv_perfex[Projected Paid Responders x Projected Average Buy],mv_perfex[Act_Prem])

I assumed that this would have just pulled the $2,220 that is sitting in the "Actual Premium" field but it is still pulling the $915.

The second attempt I did this: 

Actual Premium With 90 Day Designator = IF(MAX(mv_perfex[90 Day Date Designator])="True",mv_perfex[Projected Paid Responders x Projected Average Buy],[Paid Responders @@100% x Actual Average Buy)

I double checked and confirmed that the value of the "Paid Responders @100%" field is 15 and the value of the "Actual Average Buy" field is $148 which is $2,220.

[krichmond]

I will have to play around with this some more on Monday morning. I have to go pick my son up from daycare. I cannot thank you enough for giving me the building blocks and the information to hopefully figure this out next week. I hope you have a fantastic weekend!

[Anonymous]

no problem my friend you got this we all started somewhere have a great weekend