How to get the maximum date from two dates of same month

[Shadaiv]

Hi Experts,

Need your help here.

I am using a line graph, where in X axis i am using Dates and Y axis - Paid Amount.

Each month, the payment is made twice which is fortnighly. So in the line graph i want to show only the maximun date from the given month when the payment was made. How should i create a flag or column which will show only the maximum date from given dates of same month as shown below (Expected)

Current

Expected

Thanks

[Greg_Deckler · Accepted solution]

@Shadaiv Well, one way would be to create a Complex Selector: The Complex Selector - Microsoft Power BI Community In your case maybe:

Selector Measure = 
  VAR __CurrentDate = MAX('Table'[Date])
  VAR __Year = YEAR(__CurrentDate)
  VAR __Month = MONTH(__CurrentDate)
  VAR __Table = FILTER(ALLSELECTED('Table'), [Date] < __CurrentDate && YEAR([Date])=__Year && MONTH([Date])=__Month)
RETURN
  IF(ISBLANK(__Table),1,0)

You could also do this in a colum like:

Selector Measure = 
  VAR __CurrentDate = 'Table'[Date]
  VAR __Year = YEAR(__CurrentDate)
  VAR __Month = MONTH(__CurrentDate)
  VAR __Table = FILTER('Table', [Date] < __CurrentDate && YEAR([Date])=__Year && MONTH([Date])=__Month)
RETURN
  IF(ISBLANK(__Table),1,0)

[Shadaiv]

Hi @Greg_Deckler 

Thank you so much for your reply.

I tried both DAX column and Measure but getting below error :
"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
Measure

Column

Please let me know if i am missing something.

Thanks

[Greg_Deckler]

@Shadaiv Sorry, use ISBLANK(COUNTROWS(__Table)) in the IF condition.

[Shadaiv]

Thank you so much @Greg_Deckler . It worked 🙂