help with measures and date intervals

[Anonymous]

Hello,

I'm currently working with invoice data for the company that i work for.  One of the visuals that i need to create is a table containing the counts of unposted invoices over specific date intervals such as 0-20 days, 21-30 days etc as well as the total value of these invoices.

The aim is the table below.

Currently i'm using very inflexible formulas to work these out as measures

E.G this is what i'm using to calculate the count of unposted date for the 0-20 day interval.

0 to 20 Days = CALCULATE([Count of unposted flag], FILTER('P2P vwPurchaseInvoice', DATEDIFF([InvoiceDate], [PeriodDateSelected], DAY) >= 0 && DATEDIFF([InvoiceDate], [PeriodDateSelected], DAY) <= 20))

[PeriodDateSelected] is a measure that grabs the value of the PeriodEndDate slicer. The idea being that the values in the table should update based on the value selected in the slicer.

What i would like is to have the total amount of unposted values and then drop the date intervals onto the canvas to split the numbers into their relevant time interval 'bins'. And then the same for the value of these invoices.

Currently because of the rigid formulas used in my measures i cannot combine the value table with the count table.

Please could anyone help?

N.b i would include what the visual currently looks like but i cannot publish company data.

[amitchandak]

@Anonymous ,

You need a measure

DATEDIFF(min([InvoiceDate]), [PeriodDateSelected], DAY)

now this measure need work at the invoice or invoice line level

example

averagex(Values(Invoice[Invoice]), DATEDIFF(min([InvoiceDate]), [PeriodDateSelected], DAY) )

you need to bucket this. you need an independent table and you have to group it back at invoice or line-level again in new measures 

refer by video on the same

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k