DAX to return the result of a lesser than equal to slicer upon selecting is equal to slicer

[smpa01]

Hello experts,

I am currently working on a viz as follwoing.

When I select the period as less than equal to I get this viz.

The measures are follwing

Budget YTD = 
VAR _1 = MAX(DateTbl[Period Number])
VAR _2 = CALCULATE(CALCULATE([Budget], DateTbl[Period Number]<=_1, ALL(DateTbl[Period Number])), ALL(DateTbl[Period Name]))
RETURN _2

Actual YTD = 
VAR _1 = MAX(DateTbl[Period Number])
VAR _2 = CALCULATE(CALCULATE([Actual], DateTbl[Period Number]<=_1, ALL(DateTbl[Period Number])), ALL(DateTbl[Period Name]))
RETURN _2

Budget = SUMX(Budget,Budget[Budget])

Actual = SUMX(Actuals,Actuals[Actual])

Is there any way to get the same viz as above when the user makes a single selection as follwoing.

There are other viz on the same table which might break if I let the user make a lesser than equal to slicer selection.

I can't figure out how can I feed in that condition in those two (Budget YTD and Actual YTD measure).

A sample data would be following

Period Name	ULLG	Budget	Date
January	Opex	$5,535,316.16	1/1/2019
February	Opex	$5,595,571.34	2/1/2019
March	Opex	$5,662,290.42	3/1/2019
April	Opex	$6,542,861.36	4/1/2019
May	Opex	$5,160,639.86	5/1/2019
June	Opex	$4,521,289.08	6/1/2019
July	Opex	$4,705,957.44	7/1/2019
August	Opex	$4,119,022.64	8/1/2019
September	Opex	$4,167,515.15	9/1/2019
October	Opex	$4,826,702.24	10/1/2019
November	Opex	$6,086,002.73	11/1/2019
December	Opex	$5,500,882.34	12/1/2019

Budget Table

Period Name	ULLG	Actual	Date
January	Opex	$5,656,756.53	1/1/2019
February	Opex	$5,456,041.26	2/1/2019
March	Opex	$6,421,766.07	3/1/2019
April	Opex	$6,154,725.54	4/1/2019
May	Opex	$4,697,044.68	5/1/2019
June	Opex	$5,053,559.26	6/1/2019
July	Opex	$4,854,606.80	7/1/2019
August	Opex	$4,403,921.72	8/1/2019
September	Opex	$4,748,459.55	9/1/2019
October	Opex	$5,314,571.03	10/1/2019
November	Opex	$205,960.43	11/1/2019
December	Opex		12/1/2019

Actual Table

My date table is following

Any help is greatly appreciated

[Anonymous · Accepted solution]

Hi @smpa01 ,

In my opinion, there’re relationship between DateTbl, Actual and Budget, so you might need to create a calculated table as slicer.

Please check following steps as below and see if the result achieve your expectation:

1. Create calculated table and use Period Number as slicer:

    Table = DISTINCT(DateTbl[Period Number])

2. Create measure:

    Measure 2 =

    var sv = SELECTEDVALUE('Table'[Period Number])

    return

    IF(NOT(ISFILTERED('Table'[Period Number])),1,IF(MONTH(MAX(Actual[Date]))<=sv,1,BLANK()))

3. Add measure to filter:

4. Result would be shown as below:

BTW, Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

@Anonymousthanks for taking time out to look into this and providing a solution.

It made me realize that this requirement can only be achieved by using a disconnected table. I was hoping for a DAX magic to produce the result without having to do that but using the values in a slicer from the same table would filter out the rows that falls outside of "is equal" to range. So this requirement can't be met I gues the way I want it to happen.

Nevertheless thanks for showing me a workaround.

I also found a similar post in the community https://community.powerbi.com/t5/Desktop/Passing-Slicer-Selection-value-in-DAX-Filter/m-p/176312#M77068