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, Rolli...
  • 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

  • SP_1's avatar
    SP_1
    4 months ago

    Hi FBergamaschi ,

    Thanks for you help and its working.

SP_1's avatar
SP_1
Frequent Visitor
4 months ago

Hi FBergamaschi ,

Thanks for you help and its working.