Rolling days with reset

[Bash--]

Hi All!

I have done the same calculation for rolling days (column) and my other columns are affecting the rolling 10 days calculation and not the correct value but if I deleted the column it gives me the right value. How to exclude columns in DAX without deleting them.

Column =
VAR __date = 'Table (2)'[Date]
VAR __allResource = ALLEXCEPT( 'Table (2)', 'Table (2)'[Resource] )
VAR __firstZero =
CALCULATE(
MAX( 'Table (2)'[Date]),
__allResource,
'Table (2)'[Date] <= __date,
'Table (2)'[Billable] = 0
)
VAR __start =
IF(
ISBLANK( __firstZero ),
CALCULATE(
MIN( 'Table (2)'[Date] ),
__allResource
),
__firstZero
)
RETURN
CALCULATE(
SUM( 'Table (2)'[Billable]),
FILTER(
ALL( 'Table (2)'[Billable]),
'Table (2)'[Date] >= __start && 'Table (2)'[Date] <= __date
)
)

Any Suggestions?

Thanks,

[v-eqin-msft · Accepted solution]

Hi @Bash-- ,

Your column seems to be wrong on my side. I have tried another method, please check if it helps:

Firstly, get the date of the nearest 0:

nearest0 = MINX(FILTER('Table (2)',[Resource]=EARLIER('Table (2)'[Resource]) && [Billable]=0 && [Date]>EARLIER('Table (2)'[Date])),[Date])

Get the rolling sum:

Result = CALCULATE(SUM('Table (2)'[Billable]),FILTER('Table (2)',[Resource]=EARLIER('Table (2)'[Resource]) && [nearest0]=EARLIER('Table (2)'[nearest0])  && [Date]<=EARLIER('Table (2)'[Date])))

 Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bash--]

@parry2k Hi. Thanks for the quick reply. I tried to but date column returns grey and not available for measure 

[parry2k]

@Bash-- without looking at the details, you should be adding this as a measure not a column

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.