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.

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

SP_1's avatar
SP_1
Frequent Visitor
4 months ago

Hi FBergamaschi ,
It worked perfectly - thanks you for your help! This is exactly the result I was expecting. I’ve replicated the same in my PBIX file, and it’s working as expected. Once again, many thanks for your support.

Will the same code work for quarter and year as well? I’m currently trying to modify it for quarterly and yearly calculations.

Thanks.

  • SP_1's avatar
    SP_1
    Frequent Visitor
    4 months ago

    Hi FBergamaschi ,

    I’m able to see the Rolling MTD value in a table visual only when the Date field is included along with the Rolling MTD measure. Otherwise, it doesn’t appear in either the table or other visuals. I want to display this rolling data by Week and Month (with Week/Month on the X-axis and values on the Y-axis), but I’m not sure how to fix this. Please help.