Calculation to visual, how to filter

[rpinxt]

@Gabry helped me with the RUNINGSUM value in the new preview feature for visual calcultions.

Worked fine :

But this table was a simple view of a graph I wanted to make.

And that graph would be to big for all the periods (2018-2024) so I have filter on it to always show last 13 months.

But then the visual calculations does not work anymore :

The filter is this in my autocalendar:

"Rolling Month Nr", DATEDIFF ( [Date], TODAY (), MONTH )

So @Gabry do you or somebody else know how to ammend this calculation to ignore filters?

Using calculate in these visual calculation don't seem to work that good.

[rpinxt]

@Gabry and other, I put the .pbix file here :

https://drive.google.com/file/d/1NxvB7oyEgSeiXvh4XGUFYvHvAVtR_1Os/view?usp=sharing

Maybe that is easier if you can see the data for yourself.

[Gabry · Accepted solution]

Issue is that you have bad ralation with blank values filtered away from the visual with the filter you put.

32 complaints have no date attached and you are just not considering them.

In this case you need to adjust the measure like this:

Running sum =

VAR MaxDate = MAX (dimDate[Date]) -- Saves the last visible date

RETURN

CALCULATE (

[New-Closed],  -- Computes sales amount

dimDate[Date] <= MaxDate, -- Where date is before the last visible date

ALL ( dimDate[Date]) ,-- Removes any other filters from Date
not ISBLANK(dimDate[Date]) 

)

final result:

[rpinxt]

Omg...such a small thing giving me so much trouble 😂

Thanks this did the trick!

Gonna talk with the end user if we cannot throw out these lines without date in the query editor.

Much appreciated all your help @Gabry !

[Gabry]

Why it's not working? Because it's not starting from the point you want?

Use regular dax as i wrote you, should work good.

Running sum =
VAR MaxDate = MAX ( 'Date'[Date] ) -- Saves the last visible date
RETURN
CALCULATE (
[Sales Amount]  -- Computes sales amount
'Date'[Date] <= MaxDate, -- Where date is before the last visible date
ALL ( Date ) -- Removes any other filters from Date
)

[rpinxt]

No it should only show me last 13 months of the data set but then the formula apparently is not calculating older data anymore and thus are the amounts for the period wrong.

Used your Running sum dax but this also does not seem to ignore the filter :

Running sum =

VAR MaxDate = MAX (dimDate[Date]) -- Saves the last visible date

RETURN

CALCULATE (

[New-Closed],  -- Computes sales amount

dimDate[Date] <= MaxDate, -- Where date is before the last visible date

ALL ( dimDate[Date] ) -- Removes any other filters from Date

)

[Gabry]

I tried and for me it's working

Can you share your pbix?