Forum Discussion

SP_1's avatar
SP_1
Frequent Visitor
4 months ago
Solved

need help on dax code rolling MTD value

Hi,
Need help on getting rolling MTD value.

I have the source data attached here, need to filter only the DayName(Date) = Monday, the data is avliable on weekly.
Need output for WeekValue, RollingWeekValue, Month-to-date for the selected week, and Rolling Month-to-Date.
Here am unable to acheive to get  Rolling Month-to-Date.

Need help on code for get rolling MTD value based on MTD column.

Rolling Week Value =

CALCULATE (

    [Value],

    FILTER (

        ALLSELECTED ( 'Date' ),

        'Date'[Date] <= MAX ( 'Date'[Date] )

    )

)

 

MTD Value = CALCULATE(SUM(Value),'Date'[FiscalYear] = SELECTEDVALUE('Date'[FiscalYear]),'Date'[FiscalMonthNumber]=SELECTEDVALUE('Date'[FiscalMonthNumber]),'Date'[Date]<=SELECTEDVALUE('Date'[FiscalWeekEnd]))


Need output to get Rolling MTD value highlighted below.

 

DateDayValue
28-04-2025 00:00Mon804.28
05-05-2025 00:00Mon2937.27
12-05-2025 00:00Mon1057.96
19-05-2025 00:00Mon1714.2
26-05-2025 00:00Mon5802.43
27-05-2025 00:00Tue43.53
28-05-2025 00:00Wed2334.93
29-05-2025 00:00Thu172.35
30-05-2025 00:00Fri42.99
31-05-2025 00:00Sat3118
02-06-2025 00:00Mon2834.43
09-06-2025 00:00Mon1295.66
16-06-2025 00:00Mon4455.44
23-06-2025 00:00Mon1981.1
24-06-2025 00:00Tue4453.32
25-06-2025 00:00Wed985.11
26-06-2025 00:00Thu732.95
27-06-2025 00:00Fri509.76
28-06-2025 00:00Sat-275.61
29-06-2025 00:00Sun-5500
30-06-2025 00:00Mon186.43
07-07-2025 00:00Mon2602.33
14-07-2025 00:00Mon1482.63
  • FBergamaschi's avatar
    FBergamaschi
    4 months ago

    Hi again,

    now this result

     

     

    It matches yours apart from Fiscal week 4, but I think the above value is the correct one, am I right? Your value is 11,313

     

    Code

    Rolling MTD =
    VAR MaxDate = MAX ( 'Date'[Date] )
    VAR MaxMonth = MAX ( 'Date'[FiscalMonthFullName] )
    VAR DatesToConsider =
    ADDCOLUMNS(
    CALCULATETABLE (
                    VALUES ( 'Date'[Date] ),
                     'Date'[Date] <= MaxDate,
                     'Date'[FiscalMonthFullName] = MaxMonth,
                     REMOVEFILTERS( 'Date' )
                    ),
                    "@ROLL", IF ( NOT ISEMPTY( CALCULATETABLE('Fact') ), [Rolling WeekValue] )
    )
    RETURN
        IF (
            NOT ISEMPTY ( Fact ) && ISINSCOPE( 'Date'[Date] ),
            SUMX (
                DatesToConsider,
                [@ROLL]
                )
        )

     

    File attached

     

    If this helped, please consider giving kudos and mark as a solution

    me in replies or I'll lose your thread

    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

    Consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI

7 Replies

  • FBergamaschi's avatar
    FBergamaschi
    Super User
    4 months ago

    Hi SP_1 
    I could not replicate what you ask because I would need the association of your fiscal months to the dates which you did not provide

     

    I obtained this

     

     

     

    with this code (I created a 'Date' table )

     

    Rolling MTD =
    VAR MaxDate =
        MAX ( 'Date'[Date] )
    VAR MaxYear = YEAR ( MaxDate )
    VAR MaxMonth = MONTH ( MaxDate )
    RETURN
        IF (
            NOT ISEMPTY ( Facts ) && ISINSCOPE( 'Date'[Date] ),
            SUMX (
                CALCULATETABLE (
                    VALUES ( 'Date'[Date] ),
                    'Date'[Date] <= MaxDate,
                     YEAR ( 'Date'[Date] ) = MaxYear,
                     MONTH( 'Date'[Date] ) = MaxMonth
                    ),
                [Rolling Week Value]
                )
        )
     
    I think it should work when you slice data with your calendar where you have the link between dates and fiscal months
     
    Let me know if this works or I shall fix it if you send me your calendar too
     
    File attached
     

    If this helped, please consider giving kudos and mark as a solution

    @me in replies or I'll lose your thread

    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

    Consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI

      • FBergamaschi's avatar
        FBergamaschi
        Super User
        4 months ago

        Hi again,

        now this result

         

         

        It matches yours apart from Fiscal week 4, but I think the above value is the correct one, am I right? Your value is 11,313

         

        Code

        Rolling MTD =
        VAR MaxDate = MAX ( 'Date'[Date] )
        VAR MaxMonth = MAX ( 'Date'[FiscalMonthFullName] )
        VAR DatesToConsider =
        ADDCOLUMNS(
        CALCULATETABLE (
                        VALUES ( 'Date'[Date] ),
                         'Date'[Date] <= MaxDate,
                         'Date'[FiscalMonthFullName] = MaxMonth,
                         REMOVEFILTERS( 'Date' )
                        ),
                        "@ROLL", IF ( NOT ISEMPTY( CALCULATETABLE('Fact') ), [Rolling WeekValue] )
        )
        RETURN
            IF (
                NOT ISEMPTY ( Fact ) && ISINSCOPE( 'Date'[Date] ),
                SUMX (
                    DatesToConsider,
                    [@ROLL]
                    )
            )

         

        File attached

         

        If this helped, please consider giving kudos and mark as a solution

        me in replies or I'll lose your thread

        Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

        Consider voting this Power BI idea

        Francesco Bergamaschi

        MBA, M.Eng, M.Econ, Professor of BI