Aging Bucket - Measure on the fact table

[ells]

This has been a bit of a trial.
I have a slicer to give me the date of the end of the month on the report

The user can select the end of the month from the slicer

Then I need to look at the fact Table and work out for each row the number of days between the invoice date (on the fact table) and the end of month from the slicer. This Provides me with a measure NumberOfDays on The fact Table

Then I can create a Measure AgeBand on the fact table that is a measure that create bands of 5 days based on NumberOfDays.

Now the problem comes when I want to present this in a chart.

1. The doughnut chart (everyone loves a doughnut) does not allow a measure to be used on the legend.
2. As NumberOfDays and AgeBand are dynamic when you look at them at a total level the calculation gets a little weird.

This is different from most Aging Buckets / banding reports I have seen as they are normally static and the calculation can be done in a calculated column on the fact table rather than a measure.

Any suggestions for this I will try and replicate in a small report and publish.
Thanks
E

[Heather]

for Item #1 consider a disconnected table to use for the visual - see this post for an example of how this can be done:

How to Change table visual via a slicer

@amruthdna2018, Here’s one way to do this: create a disconnected supporting table containing your desired slicer values, in this case Disconnected Employer Table = VALUES( Employee[Employer] ) create a measure like this: Invert Slicer = IF( MAX( Employees[Employer]) IN ALLSELECTED( ‘Disconnected Employer List’[Employer] ), 1, 0 ) In one copy of the visual, set your filter pane condition for that visual like this: [image] And for the other visual, set up the same way but with t…

for item #2 you can update your measure to use a trick that removes the totals, one popular choice is to include HASONEVALUE in the measure:

Remove total sum for a column

Hi, See example, DAX formula returns TRUE when the context for ColumnName has been filtered down to one distinct value only. no total = IF(HASONEVALUE(‘Table’[Month]);[Total Amount];BLANK()) Paul

[ells]

@Heather
Thanks for that. Just worked through #2 and works a treat.

Thanks
E

[Heather]

happy to help