Change a value by a variable in measure

[frostys]

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 slicer" which column is 1,3,6,9,12.

And "Selected time" who keep the values selected by the slicer "time slicer"

Selected time = VALUES('Time slicer'[Time slicer])

But when I change "3" By my new measure :

 EOMONTH('Data input'[Date],[Selected time])>TODAY()

The visualisation don't work anymore 

Thank you for your help !

[v-jiascu-msft]

Hi @frostys,

Did you solve it? Can you share the answer or mark the proper answer as a solution please?

Best Regards,
Dale

[frostys]

Hi @v-jiascu-msft,

I didn't find the solution yet

[v-jiascu-msft]

Hi @frostys,

Isn't that formula a solution even if it can work? Can you share a dummy file? To be honest, your scenario isn't  complicated.

Best Regards,

Dale

[frostys]

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]

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

[v-jiascu-msft]

Hi @frostys,

Can you share a dummy sample of your data? Please try the demo in the attachment.

Selected time =
VAR selected =
    SELECTEDVALUE ( 'Time slicer'[Time slicer] )
RETURN
    IF ( ISBLANK ( selected ), 0, selected )

Best Regards,

Dale

[Thejeswar]

Hi @frostys,

Try using the below code

EOMONTH('Data input'[Date],SELECTEDVALUE('Time slicer'[Time slicer])>TODAY()

Here the

SELECTEDVALUE('Time slicer'[Time slicer])

will hold the value of the selection that you make in the slicer 

[frostys]

Hi @Thejeswar,

This syntax is incorrect ,

 

I think it expect a alternate result but I don't find the correct syntax

[v-jiascu-msft]

Hi @frostys,

Regarding to syntax error, you missed a ")" before ">TODAY()".

Best Regards,

Dale

[Thejeswar]

Hi,

DId you try my other suggestion?

Did it work?

[frostys]

Hi @Thejeswar,

Yes, it work only with the minimum value, "1". The other values selected with the slicer doesnt work

[Thejeswar]

@frostys,

If you can share some sample data and your expected result, may be we can help further

[Ashish_Mathur]

Hi,

Try this

Selected time = MIN('Time slicer'[Time slicer])

[frostys]

Hi Ashish_Mathur,

The visualization is now working but with just the value "1".

When I select other values (ex:3,6,9,12)  in the slicer the visual keep "1" as month

[Thejeswar]

HI,

You can also try this, if both the above solutions doesn't satisfy you

Selected time = IF(ISFILTERED('Time slicer'[Time slicer]), SELECTEDVALUE('Time slicer'[Time slicer]), MIN('Time slicer'[time slicer]))

Here 

SELECTEDVALUE() with take values when some thing is selected in slicer. If nothing is selected, the MIN() (Minimum value) will be considered for the Column

Regards,

Thejeswar

[Ashish_Mathur]

Hi,

That is due to something else then.  Share the link from where i can download your file.  Expain the business problem and show the expected result.