Forum Discussion

engineer_alvina's avatar
engineer_alvina
New Member
3 years ago

Aging dates for customers

I HAVE THE FOLLOWING TABLE WITH COMPANY NAME AND AMOUNT 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?  ...
wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
3 years ago

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

  • engineer_alvina's avatar
    engineer_alvina
    New Member
    3 years ago

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