cut off running total on visual at today

[Anonymous]

I want my visual to cut off at "Today" and the way I am doing it with my measure it is not working. I dont want to use filters because Ill have to change that every month. Is there another way to do this?

RunningRev = 
CALCULATE(
    SUM(FinancialPlans[RevChange]),
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date]<= TODAY()
    )
)

[parry2k · Accepted solution]

@Anonymous try following measure

RT =
VAR _S =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        KEEPFILTERS ( 'Calendar'[Date] <= TODAY () )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALL ( 'Calendar'[Date] ),
            'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        )
    )
        * DIVIDE ( _S, _S )

[Vvelarde]

Hi, try adding an IF condition before the rest of the measure:

IF (YourDate <= TODAY() , Yourmeasure, BLANK())

Regards

Victor

[Anonymous]

@Vvelarde 

I cant use an if statement because It does not allow me to reference the any table

I tried that before but it was frustrating and I have had that issue before.

[Vvelarde · Accepted solution]

Ok, in a measure you can't refer directly a field.  You need an aggregation or a MIN, MAX, etc.

I create a PBIX sample with some way to solve it (according to my understand of your scenario)

Regards

Victor

[Anonymous]

I see, that helps me a lot in understanding DAX

[Anonymous]

Thanks for all the help to @Mariusz and @Greg_Deckler , it helped me figure out the solution. I dont understand my the difference between adding an additional filter vs just adding it in the new filter but it works

RunningRev = 
CALCULATE(
    SUM(FinancialPlans[RevChange]),
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date]<=
            MAXX(
                FILTER(
                    'Date',
                    'Date'[Date]<=TODAY()
                ),
                'Date'[Date]
            )
    )
)

[Mariusz]

Hi @Anonymous 

You can use Measure like below.

TodayFilter = INT( MAX( 'Date'[Date] ) <= TODAY() ) 

And later use it as a filter on your visual like.

Or use the built in Relative Date Filter option.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

@MariuszThank you that does help solve part of my problem. I was also wanting to add forecasting data to the same visual so I specifically want just that measure to cutoff at Today()

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[parry2k · Accepted solution]

@Anonymous try following measure

RT =
VAR _S =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        KEEPFILTERS ( 'Calendar'[Date] <= TODAY () )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALL ( 'Calendar'[Date] ),
            'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        )
    )
        * DIVIDE ( _S, _S )

[Anonymous]

@parry2k 

Thank you! That works as just as well

[Mariusz]

Hi @Anonymous 

Try this if it works?

Measure = 
CALCULATE(
    SUM( FinancialPlans[RevChange] ),
    KEEPFILTERS( 'Date'[Date] <= TODAY() ) 
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

Hey @Anonymous 

try, UTCTODAY() instead of TODAY() 

[Anonymous]

Still same problem

[Anonymous]

@Anonymous can you share a screenshot of your model?