DAX Calculate delay per month

[DGTL]

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 !

[ERD]

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!

[DGTL]

Hi ERD, thank you so much for your answer ! But not working... 😥

View the attachment .pbix : 

https://www.transfernow.net/dl/20210326haKI2cmE 

[ERD]

Another option upon Table Dates:

overdueOrders2 = 
var currentDate2 = SELECTEDVALUE('Table Dates'[Date])
var RES2 = 
CALCULATE(
    COUNTROWS(Overdue_orders),
    FILTER(
        Overdue_orders,
        currentDate2 >= Overdue_orders[Requested date] &&
        currentDate2 <= Overdue_orders[Delivered date]))

Return
IF(ISBLANK(RES2),0,RES2)

[ERD]

! 

VAR currentDate = EOMONTH(SELECTEDVALUE('First Month'[First Month]), 0)

[DGTL]

up please 🙂

[Anonymous]

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]

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