Forum Discussion

kadapavel's avatar
kadapavel
Icon for Helper II rankHelper II
7 years ago
Solved

Rolling total since specific date

Hi, could you please help me to fix my measure.

I would calculate rolling total since one specific date

fatigue_test = 
CALCULATE(
	SUM('KPI_Indices'[Wearout with min damage]),
    FILTER(
		ALL(KPI_Indices[ResultDate]),
		'KPI_Indices'[ResultDate] <= MAX('KPI_Indices'[ResultDate])))

here I have all days selected, but I need to start from [replacement_Date].

BR

Pavel

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI kadapavel ,

    You can consider to add a if statement with conditions to compare current date and specific date:

    POwer bi DAX =
VAR currDate =
    MAX ( 'Sheet1'[ResultDate] )
VAR selected = 'specific datevalue'
RETURN
    CALCULATE (
        SUM ( 'Sheet1'[Result] ),
        FILTER (
            ALL ( Sheet1 ),
            IF (
                currDate < selected,
                'Sheet1'[ResultDate] <= currDate,
                'Sheet1'[ResultDate] >= selected
                    && 'Sheet1'[ResultDate] <= currdate
            )
        )
    )

    Regards,

    Xiaoxin Sheng

6 Replies

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

      kadapavel can you share sample data and also the expression of the measure for replacement date? it will be easier if you can share pbix , remove any sensitive info

      • kadapavel's avatar
        kadapavel
        Icon for Helper II rankHelper II
        7 years ago
        Spoiler

        Hi, I was not able to attach PBIX file,

        here is a snapshot of data, days result and cumulative total. Last column[I want this result] is that what I want and have built with excel. [Power bi DAX] is a measure where I have a challenge.

        POwer bi DAX = 
CALCULATE(
	SUM('Sheet1'[Result]),
    FILTER(
		ALL(Sheet1[ResultDate]),
		'Sheet1'[ResultDate] <= MAX('Sheet1'[ResultDate])))

        BR

        Pavel