Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Aging Group based on dateslicer

Hi!

 

New day, new challenge;)   @

 

My goal:

When i change the date slicer to a specific date, i need the data to show the amount outstanding, on that point, historical. I got that to work with these 3 measures;

 

1. selected date = MAXX(KEEPFILTERS(VALUES('Calendar'[Date])),CALCULATE(MAX('Calendar'[Date])))

2. invoicedate after selected date Y/N = IF([max invoice date] > [selected date],TRUE(),FALSE())
3. invoice amount outstanding = IF([invoicedate after selected date Y/N] = FALSE() , [Invoice_amount],0)
 
You can see the outcome in the attached printscreen below. The problem comes with the calculation of the aging group. It only works when i make a measure, see printscreen. I also tried it with a calculated column, but as you can see, the aging group is false. It shows "Due >365" on every row. I get that, because a calculated column only refreshes on reload, right? The aging groups based on the measure shows the right outcome, but only in the table. The requirement is to show it like the second printscreen. I think that only works when you use a column...how can i fix this? Does anyone have a clue?
 

Need Help

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    hi Anonymous 

               you cannot create calculated column for this requirement. Measures will show only the summarized data unless we add rows to the table visual like (invoice date, due time, etc). I suggest you to create measures seperately for due< 30 days , due 90 -180, due 180-365, due >365. Then you can visualize like below

    for ex:

     due time                       due<30    due 90-180     due 180-365    due >365

    total amount

    (on selected date)          351              256                 1246                   27

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi! tried that also, but the amount is the same in every column. Because the aging days is zero, only when I add InvoiceDate the table expands and shows the correct aging days per row. Maybe you know why?

       

      Due < 30 days = CALCULATE([SD invoice amount outstanding],FILTER(MeasureTable, [SD invoice Aging Days] >= 1 && [SD invoice Aging Days] < 30))
       
      Due >365 days = CALCULATE([SD invoice amount outstanding],FILTER(MeasureTable,[SD invoice Aging Days] > 365))
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Anonymous 

         

        use DateDiff function to calculate difference of days between the minimum date and the selected date on slicer.

        datediff = DATEDIFF(MINX('table1','table1'[Invoice Date]),CALCULATE(MAX('table1'[Invoice Date]),ALLSELECTED('Invoice Date')),DAY)+1
         
         calculate ageing in seperate measures.
        <30 = IF([datediff]<=30,SUM('Table1'[SD Invoice Amount Outstanding]),0)\
        30 to 365 = IF([datediff]>=31 && [datediff]<=365,SUM('Table1'[SD Invoice Amount Outstanding]),0)
        >365  = IF([datediff]>365,SUM('Table1'[SD Invoice Amount Outstanding]),0)
         
        finally sum all rows corresponding to ageing days
        Due < 30 days = SUMX('Table1','Table1'[<30])
         Due 30 to 365 days = SUMX('Table1','Table1'[30 to 365])
        Due > 365 days = SUMX('Table1','Table1'[>365])
         
        try this, the above as measures
         
        drag   Due < 30 days, Due 30 to 365 days, Due > 365 days   these meausres as columns to the table visual.
  • FarhanAhmed's avatar
    FarhanAhmed
    Community Champion
    6 years ago

    Unfortunately you cannot create Dynamic Column Value because Computed Column is pre-calculated. You would require a Measure to make it dynamic

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      @ 

      Yes, i know. I already made a measure, and it shows the correct data..but only in a table with the field (dimension) Invoice_date. When I remove Invoice_date all rows collapse to 1 row, also the aging groups.