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.