Calculate measure dynamically with selected Slicer date

[Puja]

Hi Experts , 

I need some help .

I am able to do the static date calculation in the below final measure .

But my user wants date calculation dynamically.

EX: If the user chose 2023 09 on the slicer he should see data like in the the below Screenshot

Test Data

YearMonth	YearMonthNumber	Actual	forecast	Final
2022 8	202208	14820197	518264	518263.7
2022 9	202209	10998212	28804136	28804136
2022 10	202210	8597500	8627975	8627975
2022 11	202211	4193907	4204442	4204442
2022 12	202212	2501551	2536261	2536261
2023 1	202301	8023156	10118130	10118130
2023 2	202302	7341592	7488866	7488866
2023 3	202303	5333733	5390168	5390168
2023 4	202304	4469925	4569401	4569401
2023 5	202305	3260933	3253736	3253736
2023 6	202306	1647479	1630904	1630904
2023 7	202307	7241252	452386	7241252
2023 8	202308	10526583	480101	10526583
2023 9	202309	8842447	452551	8842447
2023 10	202310	7999377	602118	7999377
2023 11	202311	5245406	631561	5245406
2023 12	202312	2914602	432633	2914602

Thank you in advance :).

[Anonymous · Accepted solution]

Hi @Puja ,

I suggest you to inactive or remove the relationship between your fact table and dimdate table.

Here I create an unrelated dimdate table by dax.

DimDate = 
ADDCOLUMNS( CALENDAR(DATE(2022,01,01),DATE(2023,12,31)),"YearMonthNumber",YEAR([Date])*100+MONTH([Date]))

Add a [YearMonthNumber] column in fact table.

Then try this code to create the final measure.

M_Final = 
VAR _SELECTVALUE = SELECTEDVALUE(DimDate[YearMonthNumber])
VAR _ACTUAL = CALCULATE([Actual],FILTER(Test, Test[YearMonthNumber]>=_SELECTVALUE))
VAR _RESULT = CALCULATE(IF(_ACTUAL = BLANK(),[forecast],_ACTUAL))
RETURN
_RESULT

Result is as below.

Best Regards,
Rico Zhou

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

[Puja]

Hi @Anonymous 

The measure is working as expected but is not adding the total correctly. I used SUMX, HASONEVALUE, ISINSCOPE to correct the totals but none is woorking for me.

Hope you can help me :).

Thanks much.

[Puja]

Thank you @Anonymous .

[ribisht17]

Hi @Puja ,

See if it helps, https://www.youtube.com/watch?v=3RcRqp_xgAw

Thanks,

Ritesh

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin | PL 300 Certification Series 

[amitchandak]

@Puja , The slicer of year month should come from an independent date table

example, you can build you measure based on this

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Year Month])
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Year Month] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.