Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ansar
Helper II
Helper II

MAX & MIN Date measure

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 =

CALCULATE(
MAXX(Data,ENDOFMONTH(Data[Pstng Date])),Data[Type]="BF")

ansar_0-1630055767745.png

 

Regards,

Ansar.

 

2 ACCEPTED SOLUTIONS

@ansar 

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")))

1.PNG

one more question, why 24426 is 5/26? i thought you want min date? should that be 4/30?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

@ansar 

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))))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

16 REPLIES 16
ryan_mayu
Super User
Super User

@ansar 

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'))))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@ryan_mayu Appreciate your help, i have followed as below but showing error "The MAX function only accepts a column reference as an argument."
 
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))))
 
ansar_0-1630125318385.png

Thanks,

Ansar.

@ansar 

i think you missed the closed braket for max

atest Pstng Dt = IF(MAX(Data[Type]="BF" )





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@ryan_mayu 

 

ansar_0-1630567701322.png

 

still the same error.

 

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))))
 
Regards,
Ansar.

@ansar 

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")





Did I answer your question? Mark my post as a solution!

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.

ansar_0-1630571514451.png

Regards,

Ansar.

@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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@ryan_mayu 

Output

 Agreement #Agmt TypeLatest Pstng Dt
Max11339BDA07/02/21
Max18927Punctual31/07/21
Min24426Sell Out30/04/21

 

 

Sample Data

TypePstng Date        LC amnt LCurrAgreement #Agmt Type
BF31/01/20     (706,603.70)AED11339BDA
BF29/02/20       706,603.70AED11339BDA
BF29/02/20     (536,966.65)AED11339BDA
BF31/03/20       536,966.65AED11339BDA
BF31/03/20  (1,157,831.71)AED11339BDA
RV12/03/20       216,362.47AED11339BDA
RV26/03/20       320,604.18AED11339BDA
BF30/04/20    1,157,831.71AED11339BDA
BF30/04/20  (2,065,185.54)AED11339BDA
RV12/04/20       620,865.06AED11339BDA
BF31/05/20    2,065,185.54AED11339BDA
BF31/05/20  (2,435,021.28)AED11339BDA
RV16/05/20       453,676.92AED11339BDA
BF30/06/20    2,435,021.28AED11339BDA
BF30/06/20  (2,530,466.04)AED11339BDA
RV05/06/20       145,287.86AED11339BDA
RV14/06/20       339,112.40AED11339BDA
RV17/06/20       339,112.39AED11339BDA
BF31/07/20    2,530,466.04AED11339BDA
BF31/07/20  (2,631,583.72)AED11339BDA
RV16/07/20         95,444.76AED11339BDA
BF31/08/20    2,631,583.72AED11339BDA
BF31/08/20  (2,943,629.67)AED11339BDA
RV09/08/20       101,117.68AED11339BDA
BF30/09/20    2,943,629.67AED11339BDA
BF30/09/20  (3,116,977.41)AED11339BDA
RV09/09/20       312,045.95AED11339BDA
BF31/10/20    3,116,977.41AED11339BDA
BF31/10/20  (3,115,166.70)AED11339BDA
RV31/10/20       170,000.00AED11339BDA
BF30/11/20    3,115,166.70AED11339BDA
BF30/11/20  (3,358,620.84)AED11339BDA
BF31/12/20    3,358,620.84AED11339BDA
BF31/12/20  (3,588,053.31)AED11339BDA
RV14/12/20       240,000.00AED11339BDA
RV27/01/21            4,000.00AED11339BDA
RV07/02/21       230,423.64AED11339BDA
BF30/11/20        (15,000.00)SAR18927Punctual
RV31/12/20         15,000.00SAR18927Punctual
RV31/07/21        (15,000.00)SAR18927Punctual
RV31/07/21         15,000.00SAR18927Punctual
BF30/04/21        (20,448.60)SAR24426Sell Out
BF26/05/21         20,448.60SAR24426Sell Out
BF26/05/21        (17,623.48)SAR24426Sell Out
RV30/06/21         13,098.00SAR24426Sell Out
RV31/07/21            4,525.48SAR24426Sell Out

@ansar 

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")))

1.PNG

one more question, why 24426 is 5/26? i thought you want min date? should that be 4/30?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@ryan_mayu , sorry below is the expected output, i havent consider the filter "BF" before

 

 Agreement #Agmt TypeLatest Pstng Dt
Max11339BDA31/12/20
Max18927Punctual30/11/20
Min24426Sell Out26/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

 

Expiry Dt =
IF(MAX(Data[Agmt Type]) = "BDA",CALCULATE(EDATE(ENDOFYEAR([Latest Pstng Dt 3]),6),REMOVEFILTERS(Data)),
IF(MAX(Data[Agmt Type]) = "Punctual" , CALCULATE(EDATE(ENDOFMONTH([Latest Pstng Dt 3]),12),REMOVEFILTERS(Data),
IF(MAX(Data[Agmt Type])="Sell out", CALCULATE(EDATE(ENDOFMONTH([Latest Pstng Dt 3]),6),REMOVEFILTERS(Data))))))
 
Regards,
Ansar

@ansar 

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))))

1.PNG





Did I answer your question? Mark my post as a solution!

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.

 

Expiry Dt =
if(max('Data'[Agmt Type])="Sell Out",EDATE(EOMONTH([Latest Pstng Dt 3],0),6),
if(max('Data'[Agmt Type])="BDA",EDATE(date(year([Latest Pstng Dt 3]),12,31),6),
if(max('Data'[Agmt Type])="Punctual",EDATE(EOMONTH([Latest Pstng Dt 3],0),12))))

ansar_0-1630651526132.png

ansar_1-1630651606923.png

 

Regards,

Ansar.

 

@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?

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@ryan_mayu 

 

Thanks for the help , Really appreciated !!

 

its working now, its due to some slicers i have added to report.

 

Regards,

Ansar.

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.