Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi am new to power BI, need your support.
Am trying to create a measure to get MAX Pstng Date IF Type = "BF" & Agmt Type = "BDA", "Punctual", also IF Type = "BF" & Agmt Type = "Sell out" then MIN of Pstng Date.
Prov Dt =
Regards,
Ansar.
Solved! Go to Solution.
pls try this
Measure = if(max('Table'[Agmt Type])="Sell Out",CALCULATE(min('Table'[Pstng Date]),FILTER('Table','Table'[Type]="BF")),CALCULATE(max('Table'[Pstng Date]),FILTER('Table','Table'[Type]="BF")))
one more question, why 24426 is 5/26? i thought you want min date? should that be 4/30?
Proud to be a Super User!
Is this what you want?
Measure 2 = if(max('Table'[Agmt Type])="Sell Out",EDATE(EOMONTH([Measure],0),6),if(max('Table'[Agmt Type])="BDA",EDATE(date(year([Measure]),12,31),6),if(max('Table'[Agmt Type])="Punctual",EDATE(EOMONTH([Measure],0),12))))
Proud to be a Super User!
is this what you want?
Measure = if(max('Table'[Type])="BF"&&(max('Table'[Agmt Type])="BDA"||max('Table'[Agmt Type])="Punctual"),CALCULATE(max('Table'[PstingDate]),ALL('Table')),if(max('Table'[Type])="BF"&&max('Table'[Agmt Type])="Sell Out",CALCULATE(min('Table'[PstingDate]),ALL('Table'))))
Proud to be a Super User!
Thanks,
Ansar.
i think you missed the closed braket for max
atest Pstng Dt = IF(MAX(Data[Type]="BF" )
Proud to be a Super User!
still the same error.
maybe try this
Latest Pstng Dt =
IF (
MAX ( Data[Type] ) = "BF"
&& (
MAX ( Data[Agmt Type] ) = "BDA"
|| MAX ( Data[Agmt Type] ) = "Punctual"
),
CALCULATE ( MAX ( Data[Pstng Date] ), ALL ( Data ) ),
IF (
MAX ( Data[Type] ) = "BF"
&& MAX ( Data[Agmt Type] ) = "Sell out",
CALCULATE ( MIN ( Data[Pstng Date] ), ALL ( Data ) )
)
)
the closed brakets are in the wrong postion. max(column)="xx", not max(column="xx")
Proud to be a Super User!
@ryan_mayu Thanks for correcting me, still not working as expected.
Some are showing blank values also it is passing only one value that is maxium date value from column pstng date.
Regards,
Ansar.
something need to be clarified.
1. is the agreement same as the type column in your first screenshot?
2. there is a type RV in your screenshot, but you didn't mention the output for that.
Could you pls provide the expected output based on the sample data you provided?
Proud to be a Super User!
Output
Agreement # | Agmt Type | Latest Pstng Dt | |
Max | 11339 | BDA | 07/02/21 |
Max | 18927 | Punctual | 31/07/21 |
Min | 24426 | Sell Out | 30/04/21 |
Sample Data
Type | Pstng Date | LC amnt | LCurr | Agreement # | Agmt Type |
BF | 31/01/20 | (706,603.70) | AED | 11339 | BDA |
BF | 29/02/20 | 706,603.70 | AED | 11339 | BDA |
BF | 29/02/20 | (536,966.65) | AED | 11339 | BDA |
BF | 31/03/20 | 536,966.65 | AED | 11339 | BDA |
BF | 31/03/20 | (1,157,831.71) | AED | 11339 | BDA |
RV | 12/03/20 | 216,362.47 | AED | 11339 | BDA |
RV | 26/03/20 | 320,604.18 | AED | 11339 | BDA |
BF | 30/04/20 | 1,157,831.71 | AED | 11339 | BDA |
BF | 30/04/20 | (2,065,185.54) | AED | 11339 | BDA |
RV | 12/04/20 | 620,865.06 | AED | 11339 | BDA |
BF | 31/05/20 | 2,065,185.54 | AED | 11339 | BDA |
BF | 31/05/20 | (2,435,021.28) | AED | 11339 | BDA |
RV | 16/05/20 | 453,676.92 | AED | 11339 | BDA |
BF | 30/06/20 | 2,435,021.28 | AED | 11339 | BDA |
BF | 30/06/20 | (2,530,466.04) | AED | 11339 | BDA |
RV | 05/06/20 | 145,287.86 | AED | 11339 | BDA |
RV | 14/06/20 | 339,112.40 | AED | 11339 | BDA |
RV | 17/06/20 | 339,112.39 | AED | 11339 | BDA |
BF | 31/07/20 | 2,530,466.04 | AED | 11339 | BDA |
BF | 31/07/20 | (2,631,583.72) | AED | 11339 | BDA |
RV | 16/07/20 | 95,444.76 | AED | 11339 | BDA |
BF | 31/08/20 | 2,631,583.72 | AED | 11339 | BDA |
BF | 31/08/20 | (2,943,629.67) | AED | 11339 | BDA |
RV | 09/08/20 | 101,117.68 | AED | 11339 | BDA |
BF | 30/09/20 | 2,943,629.67 | AED | 11339 | BDA |
BF | 30/09/20 | (3,116,977.41) | AED | 11339 | BDA |
RV | 09/09/20 | 312,045.95 | AED | 11339 | BDA |
BF | 31/10/20 | 3,116,977.41 | AED | 11339 | BDA |
BF | 31/10/20 | (3,115,166.70) | AED | 11339 | BDA |
RV | 31/10/20 | 170,000.00 | AED | 11339 | BDA |
BF | 30/11/20 | 3,115,166.70 | AED | 11339 | BDA |
BF | 30/11/20 | (3,358,620.84) | AED | 11339 | BDA |
BF | 31/12/20 | 3,358,620.84 | AED | 11339 | BDA |
BF | 31/12/20 | (3,588,053.31) | AED | 11339 | BDA |
RV | 14/12/20 | 240,000.00 | AED | 11339 | BDA |
RV | 27/01/21 | 4,000.00 | AED | 11339 | BDA |
RV | 07/02/21 | 230,423.64 | AED | 11339 | BDA |
BF | 30/11/20 | (15,000.00) | SAR | 18927 | Punctual |
RV | 31/12/20 | 15,000.00 | SAR | 18927 | Punctual |
RV | 31/07/21 | (15,000.00) | SAR | 18927 | Punctual |
RV | 31/07/21 | 15,000.00 | SAR | 18927 | Punctual |
BF | 30/04/21 | (20,448.60) | SAR | 24426 | Sell Out |
BF | 26/05/21 | 20,448.60 | SAR | 24426 | Sell Out |
BF | 26/05/21 | (17,623.48) | SAR | 24426 | Sell Out |
RV | 30/06/21 | 13,098.00 | SAR | 24426 | Sell Out |
RV | 31/07/21 | 4,525.48 | SAR | 24426 | Sell Out |
pls try this
Measure = if(max('Table'[Agmt Type])="Sell Out",CALCULATE(min('Table'[Pstng Date]),FILTER('Table','Table'[Type]="BF")),CALCULATE(max('Table'[Pstng Date]),FILTER('Table','Table'[Type]="BF")))
one more question, why 24426 is 5/26? i thought you want min date? should that be 4/30?
Proud to be a Super User!
@ryan_mayu , sorry below is the expected output, i havent consider the filter "BF" before
Agreement # | Agmt Type | Latest Pstng Dt | |
Max | 11339 | BDA | 31/12/20 |
Max | 18927 | Punctual | 30/11/20 |
Min | 24426 | Sell Out | 26/05/21 |
Thanks @ryan_mayu for the help,
Need a bit more help,
Measure = if(max('Table'[Agmt Type])="Sell Out",CALCULATE(min('Table'[Pstng Date]),FILTER('Table','Table'[Type]="BF")),CALCULATE(max('Table'[Pstng Date]),FILTER('Table','Table'[Type]="BF")))
am trying to create a measure using above measure date with below criteria ,
if Agrmnt Type = Sellout , then EOMONTH(Measure) + 6 Months
if Agrmnt Type = Punctual , then EOMONTH(Measure) + 12 Months
if Agrmnt Type = BDA , then ENDOFYEAR(Measure) + 6Months
Is this what you want?
Measure 2 = if(max('Table'[Agmt Type])="Sell Out",EDATE(EOMONTH([Measure],0),6),if(max('Table'[Agmt Type])="BDA",EDATE(date(year([Measure]),12,31),6),if(max('Table'[Agmt Type])="Punctual",EDATE(EOMONTH([Measure],0),12))))
Proud to be a Super User!
@ryan_mayu New measure (expiry dt) is working , but the measure (latest pstng dt) showing blank values if we add new measure to table.
Regards,
Ansar.
the measure is based on the [Latest Pstng Dt 3), if that is blank, the measure will return blank as well. should that measure based on expiry Dt?
Proud to be a Super User!
Thanks for the help , Really appreciated !!
its working now, its due to some slicers i have added to report.
Regards,
Ansar.
you are welcome
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |