Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

help with measures and date intervals

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.
 
 
 
Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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