Cumulative Total till Certain Date

[jnnilesh29]

Hello Everyone, 

I'm new to PowerBI and trying to look for a DAX formula for the cumulative value till a certain month. 

I have a data till June 2021 but I want to show the cumulative value till May 2020 only. 

I'm using the following formula which is showing the all values

Cumulative Earned Labor Units = 
CALCULATE(
    [Planned Cost],
    FILTER(
        ALL('Calendar'[Date]),
        'Calendar'[Date]<=MAX('Calendar'[Date]
    )
))

Could somebody help me to show the cumulative value till May 2020 only?

Your help is appreciated.

Thanks and Regards,

Nilesh Jain 

[mahoneypat]

Please try this expression

Cumulative Earned Labor Units =
VAR __thismax =
    MAX ( 'Calendar'[Date] )
VAR __total =
    CALCULATE (
        [Planned Cost],
        FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] <= __thismax )
    )
RETURN
    IF ( __thismax <= DATE ( 2020, 5, 30 ), __total )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[jnnilesh29]

Thanks for your help, Pat.

However, the code is not working and I'm getting the following error.

Also, I changed _thisdate to _thismax but then I'm not able to add the measure under the value/line value section of any visualization.

[mahoneypat]

I fixed the original reply.  __thisdate should have been __total.

Regards,

Pat

[jnnilesh29]

Great.

It did work but I can see the value till 2019 only despite we have (2020,05,30)

I tried the same with (2021,05,30) and I can see the value till 2020. Is this normal?

Please let me know. 

Also, I appreciate your quick reply, Pat.

Regards,

Nilesh Jain

[mahoneypat]

Since you are using the Year column in your visual, use DATE(2020, 12, 31).

Regards,

Pat

[parry2k]

@jnnilesh29 you can also try something like this

Cumulative Earned Labor Units =
VAR __stopDate = DATE ( 2020, 12, 31 ) --give the date where you want to stop the total
RETURN
CALCULATE (
      CALCULATE (
        [Planned Cost],
        FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    ),
    'Calendar'[Date] <= __stopDate 
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.⚡

[jnnilesh29]

Hi parry2k, 

Thanks, I did work and now the cumulative value is correctly reflected.

However, in the visualization a straight line is seen after the stop date. 

My stop date is May 29, 2020. 

Is there a way we stop the line after the stop date?

I'm looking forward to hearing from you.

[parry2k]

@jnnilesh29 I don't expect it to continue and should stop at May 05th, 2020. Something else is going on there, can you share pbix file, remove any sensitive information before sharing.

[parry2k]

@jnnilesh29 try one change and if it doesn't work, send pbix

Cumulative Earned Labor Units =
VAR __stopDate = DATE ( 2020, 12, 31 ) --give the date where you want to stop the total
RETURN
CALCULATE (
      CALCULATE (
        [Planned Cost],
        FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    ),
    KEEPFILTERS ( 'Calendar'[Date] <= __stopDate )
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.⚡

[jnnilesh29]

Hi Parvinder,

The line still continues beyond the stop date. 

I have emailed you the pdix file.

I'm looking forward to hearing from you.

Regards,

Nilesh Jain

[parry2k]

@jnnilesh29 no problem, haven't received yet. Will take a look as soon as I have it.

[jnnilesh29 · Accepted solution]

Thanks Parvinder for you help.

2Cumulative Planned Labor Units = 
VAR __stopDate = DATE ( 2020, 05, 29 )
RETURN
CALCULATE (
      SUMX (
        FILTER ( 
            ALL ( 'Calendar'[Date] ), 
            'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        ),
        [Planned Cost]
    ),
    KEEPFILTERS ( 'Calendar'[Date] <= __stopDate )
)

 The above code sent by you did work. I really appreciate your help. 

@parry2k