Forum Discussion

ganchevd's avatar
ganchevd
Icon for Helper I rankHelper I
8 years ago
Solved

Defining overdues towards a dynamic filtered date

Dear All,

 

I had stuck with this. Since I'm a Financial guy not an SQL expert. I have built a report for Account Receivables where I have the following columns:

1. Customer;

2. Document Number;

3. Document date/ Invoice date;

4. Amout;

5. Due date.

 

Now I put them on a nice chart where I have a slicer to chose for what period of time I'd like to see the overdues. I cannot think of formula to create a dynamic column called "Status" where it shold return "Overdue", "OK". Whenever I change the date filter I want to receive the correct status towards the corresponding date. The data is imported from SQL Database.

 

I'd be very thankful if someone help me with this dynamic slicer. Thank you all in advance.

  • ganchevd

    In order to get a dynamic date ,you can create a calendar table with the DAX below:

     

     

    calendar =
    CALENDAR ( MIN ( 'sample'[due date] ), MAX ( 'sample'[due date] ) )

     

     

    Then create two more measures

     

     

    selected date =
    IF ( HASONEVALUE ( 'calendar'[date] ), MAX ( 'calendar'[date] ), TODAY () )
    

     

    state =
    IF ( 'calendar'[selected date] > MAX ( 'sample'[due date] ), "overdue", "ok" )

     

     

  • ganchevd's avatar
    ganchevd
    8 years ago

    Eric_Zhang

     

    Thank you! It seems quite easy. But now I lost the Customers names. When I add the Status column in the visual table, the customers names disapear and the status is either OK or Overdue to all of the entries.

4 Replies

  • Hi ganchevd,

     

    Please give us sample data ( easy to copy and paste or to manipulate) with your expected outcome and we will try to help.

     

    Thank you.

     

    Ninter

    • ganchevd's avatar
      ganchevd
      Icon for Helper I rankHelper I

      Hi,

       

      Here is some sample data

       

      Customer nameDocument No.:Dodument dateAmountDue date
      Private Consult ltd100067198110.08.20171 590.0025.08.2017
      Mylander100067198212.08.20173 100.0027.08.2017
      TechTrader100067198312.08.2017120.0027.08.2017
      GanchevSports100067198415.08.20174 500.0030.08.2017
      Plan-B JSC100067198520.08.20171 000.0004.09.2017
      Truckvaley100067198615.09.2017300.0030.09.2017
      Autoparts.com100067198720.09.201715 000.0005.10.2017
      Auxentrix100067198820.09.20177 359.0005.10.2017

       

       

      Not sure how to drop it here. Thanks!

       

      Deyan

      • Eric_Zhang's avatar
        Eric_Zhang
        Icon for Microsoft Employee rankMicrosoft Employee

        ganchevd

        In order to get a dynamic date ,you can create a calendar table with the DAX below:

         

         

        calendar =
        CALENDAR ( MIN ( 'sample'[due date] ), MAX ( 'sample'[due date] ) )

         

         

        Then create two more measures

         

         

        selected date =
        IF ( HASONEVALUE ( 'calendar'[date] ), MAX ( 'calendar'[date] ), TODAY () )
        

         

        state =
        IF ( 'calendar'[selected date] > MAX ( 'sample'[due date] ), "overdue", "ok" )