topic Set default current year monthly values when options of slicer are not selected. in DAX Commands and Tips

Set default current year monthly values when options of slicer are not selected.

Anonymous — Mon, 23 Oct 2023 10:46:08 GMT

Hi guys, I have 2 tables - Date_ref and Asset. Date_ref is date table. Asset have monthly total asset value in which there are data from Jan 2021 - Aug 2023. I used a slider by year, if a year is selected, the monthly total asset values are displayed.

When select 2023 in slider, it displays Jan-Aug

I want to show data of current year (2023) as default when there is no tile in slicer selected, so i try writing the DAX below:

Total Asset =
VAR max_year = CALCULATE(MAX(Dates_ref[Year]))

VAR asset = CALCULATE(SUM(Asset[Value]), Asset[Attribute] = "Total Asset")

VAR default_asset = CALCULATE(SUM(Asset[Value]), Asset[Attribute] = "Total Asset", Dates_ref[Year] = max_year)

Return IF(NOT(ISFILTERED(Dates_ref)), default_asset, asset)

However the output is not as expected

When I do not select any tile in slider, output display incorrect values, it also display 12 months while 2023 just have Jan - Aug.

How to show data of current year (2023) as default, Is there any problem with IF or ISFILTERED?

Thank you in advance!

Re: Set default current year monthly values when options of slicer are not selected.

Walter_W2022 — Mon, 23 Oct 2023 12:27:54 GMT

I would suggest using SELECTEDVALUE instead of ISFILTERED, could you please try below?

Total Asset =
VAR _selectedYear = SELECTEDVALUE(Dates_ref[Year])
VAR max_year = CALCULATE(MAX(Dates_ref[Year]))

VAR asset = CALCULATE(SUM(Asset[Value]), Asset[Attribute] = "Total Asset")

VAR default_asset = CALCULATE(SUM(Asset[Value]), Asset[Attribute] = "Total Asset", Dates_ref[Year] = max_year)

Return
IF(NOT(_selectedYear), default_asset, asset)

Re: Set default current year monthly values when options of slicer are not selected.

Anonymous — Mon, 23 Oct 2023 16:25:54 GMT

It's worked, thank you so much.