Forum Discussion
Calculation to visual, how to filter
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:
Using calculate in these visual calculation don't seem to work that good.
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 dateRETURNCALCULATE ([New-Closed], -- Computes sales amountdimDate[Date] <= MaxDate, -- Where date is before the last visible dateALL ( dimDate[Date]) ,-- Removes any other filters from Date
not ISBLANK(dimDate[Date]))final result:
6 Replies
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
)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 dateRETURNCALCULATE ([New-Closed], -- Computes sales amountdimDate[Date] <= MaxDate, -- Where date is before the last visible dateALL ( dimDate[Date] ) -- Removes any other filters from Date)I tried and for me it's working
Can you share your pbix?
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.
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 dateRETURNCALCULATE ([New-Closed], -- Computes sales amountdimDate[Date] <= MaxDate, -- Where date is before the last visible dateALL ( dimDate[Date]) ,-- Removes any other filters from Date
not ISBLANK(dimDate[Date]))final result:
