Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Rolling days with reset

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,

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    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.

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    Anonymous 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    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.