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
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
Rolling MTD.pbix959 KB
FBergamaschi
4 months ago
HI SP_1

modified the code accordingly. File attached

Best

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
Rolling MTD.pbix959 KB
SP_1
4 months ago
Hi FBergamaschi ,

Thanks for you help and its working.

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.