Forum Discussion

Nihed's avatar
Nihed
Helper III
4 years ago
Solved

Need help dax

Hello,

 

I need your help quickly please
I wanted to do the calculation of the next measurement and I did not find the right formula dax 😕

The formula and the following :

Open Missions Calculation:

Count nr of trips where end date is before ( end of current month – 42 days ) and approved / settled date is empty or after the end of current month

Current month: if selected quarter is in the past, end month of selected quarter, else if selected quarter is current quarter, then current calendar month.

I give you the exemple of the data I have in power BI in the following Excel file :

Approved / Settled DateEnd DateStart DateNumber of TripsQuarter (Approved/Settled)Quarter (Start Date)Quarter (End Date)Year(Approved/Settled)Year(Start Date)Month( Start date)
23-août-2125-juil-2114-juil-211Q3 2021Q3 2021Q3 202120212021July
24-août-2102-août-2126-juil-211Q3 2021Q3 2021Q3 202120212021July
25-août-2112-juil-2107-juil-211Q3 2021Q3 2021Q3 202120212021July
30-août-2117-mai-2112-mai-211Q3 2021Q2 2021Q2 202120212021May
30-août-2120-juin-2117-juin-211Q3 2021Q2 2021Q2 202120212021June
30-août-2110-juil-2107-juil-211Q3 2021Q3 2021Q3 202120212021July
30-août-2125-juil-2121-juil-211Q3 2021Q3 2021Q3 202120212021July
31-août-2127-août-2126-août-211Q3 2021Q3 2021Q3 202120212021August
03-sept-2109-juil-2107-juil-211Q3 2021Q3 2021Q3 202120212021July
06-sept-2120-juin-2117-juin-211Q3 2021Q2 2021Q2 202120212021June
06-sept-2111-juil-2107-juil-211Q3 2021Q3 2021Q3 202120212021July
07-sept-2130-juin-2129-juin-211Q3 2021Q2 2021Q2 202120212021June
07-sept-2105-sept-2101-sept-211Q3 2021Q3 2021Q3 202120212021September
08-sept-2130-juil-2128-juil-211Q3 2021Q3 2021Q3 202120212021July
09-sept-2122-juil-2117-juil-211Q3 2021Q3 2021Q3 202120212021July
10-sept-2112-juil-2106-juil-211Q3 2021Q3 2021Q3 202120212021July
14-sept-2123-août-2124-juil-211Q3 2021Q3 2021Q3 202120212021July
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Nihed ,

     

    Please try the following formula:

     

    Measure = 
VAR NowQua =
    YEAR ( TODAY () ) & " Q" & QUARTER ( TODAY () )
VAR CurrentMonth =
    IF (
        SELECTEDVALUE ( 'Calendar'[Quarter] ) = NowQua,
        FORMAT ( TODAY (), "yyyymm" ),
        MAX ( 'Calendar'[YearMonth] )
    )
VAR EndDate =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        'Calendar'[YearMonth] = CurrentMonth,
        ALL ( 'Calendar' )
    )
VAR StartDate = EndDate - 42
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[End Date] <= StartDate
                && (
                    'Table'[Approved / Settled Date] >= EndDate
                        || 'Table'[Approved / Settled Date] = BLANK ()
                )
        )
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi Nihed ,

     

    Please try the following formula:

     

    Measure = 
VAR NowQua =
    YEAR ( TODAY () ) & " Q" & QUARTER ( TODAY () )
VAR CurrentMonth =
    IF (
        SELECTEDVALUE ( 'Calendar'[Quarter] ) = NowQua,
        FORMAT ( TODAY (), "yyyymm" ),
        MAX ( 'Calendar'[YearMonth] )
    )
VAR EndDate =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        'Calendar'[YearMonth] = CurrentMonth,
        ALL ( 'Calendar' )
    )
VAR StartDate = EndDate - 42
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[End Date] <= StartDate
                && (
                    'Table'[Approved / Settled Date] >= EndDate
                        || 'Table'[Approved / Settled Date] = BLANK ()
                )
        )
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.