Forum Discussion

frostys's avatar
frostys
Icon for Helper I rankHelper I
8 years ago

Change a value by a variable in measure

Hi everyone,   I try change this value ("3") by a variable :       EOMONTH('Data input'[Date],3)>TODAY() I want to choose with a slicer the number of month. I have created a new table "Time sl...
Need Help
frostys's avatar
frostys
Icon for Helper I rankHelper I
8 years ago

Maybe the solution working, but not for me. Could be a different problem. So I can't tell if this is the right formula yet.

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi frostys,

 

The root cause is that we can't use a slicer to change the values of a Calculated table. That's why these solutions here can't work in your scenario. The workaround could be like below. Please give it a try.

1. Create a measure, filter out the blank values.

Measure =
VAR CustomersPurchasedLast3Months =
    SELECTCOLUMNS (
        FILTER (
            'Data input',
            EOMONTH ( 'Data input'[Date], [Selected time] ) > TODAY ()
        ),
        "Customer", [Controlling Customer Name]
    )
RETURN
    IF (
        MIN ( 'Data input'[Controlling Customer Name] ) IN CustomersPurchasedLast3Months,
        BLANK (),
        1
    )

2. The other function that count the numbers of customers also needs to be changed. 

customerAmount =
VAR CustomersPurchasedLast3Months =
    SELECTCOLUMNS (
        FILTER (
            'Data input',
            EOMONTH ( 'Data input'[Date], [Selected time] ) > TODAY ()
        ),
        "Customer", [Controlling Customer Name]
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Data input'[Controlling Customer Name] ),
        NOT 'Data input'[Controlling Customer Name] IN CustomersPurchasedLast3Months,
        ALL ( 'Data input' )
    )

Best Regards,

Dale