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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors