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; ...
Need Help
Tips and Tricks
  • Eric_Zhang's avatar
    Eric_Zhang
    8 years ago

    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.

Interkoubess's avatar
Interkoubess
Icon for Solution Sage rankSolution Sage
8 years ago

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
    8 years ago

    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
      8 years ago

      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
        Icon for Helper I rankHelper I
        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.