The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello Power BI community,
I am trying to assess the appropriateness of the credit limits given to customers and in order to do so, I have identified the maximum daily AR balance in recent years, and to accompany the max AR balance formula, I've identified the dates of the max AR balances for each customer.
For some of the customers, concatenateX max dates could be a long list of consecutive dates, and I am wondering if I can express those consecutive dates simply as m/d/yyyy - m/d/yyyy instead of list of dates as shown below. I'd greatly appreciate it if anyone could let me know how I can tweak the formula above to shorten the text output of max consecutive dates.
Thank you.
Solved! Go to Solution.
pls try this
Max Day =
VAR viable = VALUES('calendar'[Date] )
VAR MaxDailyAR = [Max daily AR]
VAR Keepmax = FILTER( viable, [AR since Oct 2020] =MaxDailyAR )
VAR max.day = IF (
MaxDailyAR > 0,
IF (
COUNTROWS ( Keepmax ) > 1,
MINX ( Keepmax, [Date] ) & "-"
& MAXX ( Keepmax, [Date] ),
MINX ( Keepmax, [Date] )
)
)
RETURN
max_day
pls try this
Max Day =
VAR viable = VALUES('calendar'[Date] )
VAR MaxDailyAR = [Max daily AR]
VAR Keepmax = FILTER( viable, [AR since Oct 2020] =MaxDailyAR )
VAR max.day = IF (
MaxDailyAR > 0,
IF (
COUNTROWS ( Keepmax ) > 1,
MINX ( Keepmax, [Date] ) & "-"
& MAXX ( Keepmax, [Date] ),
MINX ( Keepmax, [Date] )
)
)
RETURN
max_day
Thank you @Ahmedx . It works, although non-consecutive days are minxed and maxxed even though they are not consecutive, but I guess many patchy concatenated texts of m/d/yyyy-m/d/yyyy will not make it easier to understand so I will stick with this solution. Thanks a lot.