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/202...
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!

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

    ! 

    VAR currentDate = EOMONTH(SELECTEDVALUE('First Month'[First Month]), 0)
  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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)