Forum Discussion

DGTL's avatar
DGTL
Icon for Helper I rankHelper I
5 years ago

DAX Calculate delay per month

 

Hello,

Can you unlock me on a Dax measure?


I would like to have a monthly bar chart with a calculation of overdue orders each month.

Example:
The customer requests a delivery for the 02/01/2021 (DELIVERY REQUESTED DATE)
Delivery is delivered late on 03/15/2021 (DELIVERY DATE)

My histogram should look like this





Thank you so much !

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi,

     

    you can create 2 new columns to solve your problem. First create a column with Overdue, like this:

     

    OVERDUE = IF('Table'[DELIVERY DATE].[Date] > 'Table'[DELIVERY REQUESTED DATE].[Date],1,0)

     

    and then the month form the table you want to call "late", I used the actual delivery date for mine:

     

    MONTH = MONTH('Table'[DELIVERY DATE].[Date])

     

    After that you can simply create the histogram with the sum of OVERUDE on Y axis and MONTH on X axis.

    • DGTL's avatar
      DGTL
      Icon for Helper I rankHelper I
      5 years ago

      Thanks Ascarim, 

       

      But not working...  if the delivery date have several months late, I would not see it in this case. It must be represented on each late month. Like in the first picture

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hello DGTL ,

    One of the options to achieve your result:

    1. Create a calendar table with months (first day of each month). This table will be used for your X axis. No relations are needed.

    Example:

    [Date] (month/day/year)

    5/1/2020
    6/1/2020
    7/1/2020
    8/1/2020
    9/1/2020
    etc

    2. Create a measure:

    Overdue orders = 
VAR currentDate = EOMONTH(SELECTEDVALUE('Calendar'[Date]), 0)
VAR result = 
CALCULATE(
    COUNTROWS(Overdue_orders),
    FILTER(Overdue_orders,
        currentDate >= Overdue_orders[Requested date]  &&
        currentDate <= EOMONTH(Overdue_orders[Delivered date], 0) &&
        Overdue_orders[Requested date] <> Overdue_orders[Delivered date]
    )
)
Return
IF(ISBLANK(result),0,result)

     

    Did I answer your question? Mark my post as a solution!