Forum Discussion

rpinxt's avatar
rpinxt
Solution Sage
2 years ago
Solved

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 b...
Need Help
  • Gabry's avatar
    Gabry
    2 years ago

    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:

     

     

Gabry's avatar
Gabry
Super User
2 years ago

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's avatar
rpinxt
Solution Sage
2 years ago

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's avatar
    Gabry
    Super User
    2 years ago

    I tried and for me it's working

    Can you share your pbix?