topic Re: DAX Calculate delay per month in DAX Commands and Tips

DAX Calculate delay per month

DGTL — Thu, 18 Mar 2021 13:49:05 GMT

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 !

Re: DAX Calculate delay per month

Anonymous — Thu, 18 Mar 2021 14:30:40 GMT

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.

Re: DAX Calculate delay per month

DGTL — Thu, 18 Mar 2021 15:42:54 GMT

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

Re: DAX Calculate delay per month

DGTL — Wed, 24 Mar 2021 07:18:25 GMT

up please 🙂

Re: DAX Calculate delay per month

ERD — Fri, 26 Mar 2021 16:14:51 GMT

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!

Re: DAX Calculate delay per month

DGTL — Fri, 26 Mar 2021 16:44:06 GMT

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

View the attachment .pbix :

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

Re: DAX Calculate delay per month

ERD — Fri, 26 Mar 2021 17:38:29 GMT

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

Re: DAX Calculate delay per month

ERD — Fri, 26 Mar 2021 17:44:53 GMT

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)