Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
kadapavel
Helper II
Helper II

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

1 ACCEPTED SOLUTION

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

6 REPLIES 6
parry2k
Super User
Super User

@kadapavel replacement date column is in the same table?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Spoiler
Capture1.PNG

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi v-shex-msft

Hi

[Replacement_date] is a measure

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.