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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
engineer_alvina
New Member

Aging dates for customers

AMT.PNG

I HAVE THE FOLLOWING TABLE WITH COMPANY NAME AND AMOUNT

DB.PNG

The above is the database showing the due date. what measures should I use to produce a report that is similar to the one below? 

WHAT I WANT.PNG

2 REPLIES 2
wdx223_Daniel
Super User
Super User

1create a new table without relationships

DynamicDimensionTable = VAR _last=EOMONTH(TODAY(),-1) VAR _s=EOMONTH(_last,-3)+1 VAR _Dates=ADDCOLUMNS(CALENDAR(MIN(SampleData[DueDate]),MAX(SampleData[DueDate])),"Periods",IF([Date]>_last,"Not Due",IF([Date]<_s,"Before "&FORMAT(_s,"yyyy-mm-dd"),FORMAT(EOMONTH([Date],-1)+1,"yyyy-mm-dd")&".."&FORMAT(EOMONTH([Date],0),"yyyy-mm-dd")))) RETURN UNION(SELECTCOLUMNS(GROUPBY(_Dates,[Periods],"Start",MINX(CURRENTGROUP(),[Date]),"End",MAXX(CURRENTGROUP(),[Date])),"Periods",[Periods],"Start",[Start],"End",[End]),{("Balance","","")})

  2 put the Periods into the column area of the visual

3 create a measure

Aged Amount=VAR _min=MIN(

DynamicDimensionTable[Start]) VAR _max=MAX(DynamicDimensionTable[End]) RETURN 

IF(MAX(DynamicDimensionTable[Periods]="Balance",SUM(SampleData[Amount]),CALCULATE(SUM(SampleData[Amount]),SampleData[DueDate]>=_min&&SampleData[DueDate]<=_max))

IF(MAX(DynamicDimensionTable[Periods]="Balance",SUM(SampleData[Amount]),CALCULATE(SUM(SampleData[Amount]),SampleData[DueDate]>=_min&&SampleData[DueDate]<=_max)) The highlighted part is not working its giving this error Unexpected paramater',CALCULATE(SUM(SampleData[Amount]),SampleData[DueDate]>=_min&&SampleData[DueDate]<=_max))'

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors