Forum Discussion

JothiG's avatar
JothiG
Icon for Helper III rankHelper III
1 year ago
Solved

Dax requirement

here, i have year and month slicer and other more slicer. my requirement is, if i didn't select year and month slicer above age wise stacked area chart should show the max year and max month value. if i select year and month, it should show the select year and month value. how should i write dax for this . column names are shown below

sl.No. ,Div_name, Category ,Item ,fin_year ,Stock_finyear ,stock_year, stock_month, Stock_month_name ,month_sorter, Period Inw_No ,Inw_date, Qty, UOm, rate, value, stock_month_edat ,Age_value
1 HO RM Yarn - Disposable Stock 2020 2023 2024 5 May 5 Q1 3280 2020-08-10 12.1 KGS 231.6 2802.36 1900-01-01 Disposable
2 HO RM Yarn - Disposable Stock 2020 2023 2024 5 May 5 Q1 4891 2020-09-19 31.5 KGS 227.26 7158.69 1900-01-01 Disposable
3 HO RM Yarn - Disposable Stock 2020 2023 2024 5 May 5 Q1 6879 2020-11-23 14.35 KGS 316.266202 4538.42 1900-01-01 Disposable

Need Help
  • MohamedFowzan1's avatar
    MohamedFowzan1
    1 year ago

    Hi JothiG 

     

    For this you would have to use a measure for Year & Month as well and then calculate the value dynamically.

    SelectedYear =
IF(
    ISBLANK(SELECTEDVALUE('Table'[stock_year])),
    CALCULATE(MAX('Table'[stock_year]), ALL('Table')),
    SELECTEDVALUE('Table'[stock_year])
)


     

    SelectedMonth =
IF(
    ISBLANK(SELECTEDVALUE('Table'[stock_month])),
    CALCULATE(MAX('Table'[stock_month]), FILTER('Table', 'Table'[stock_year] = [SelectedYear])),
    SELECTEDVALUE('Table'[stock_month])
)

     

    CALCULATE(
    [Value],
    'Table'[stock_year] = [SelectedYear],
    'Table'[stock_month] = [SelectedMonth]
)

     
    Please use the corresponding table names

2 Replies

  • MohamedFowzan1's avatar
    MohamedFowzan1
    Icon for Super User rankSuper User
    1 year ago

    Hi JothiG 

     

    For this you would have to use a measure for Year & Month as well and then calculate the value dynamically.

    SelectedYear =
IF(
    ISBLANK(SELECTEDVALUE('Table'[stock_year])),
    CALCULATE(MAX('Table'[stock_year]), ALL('Table')),
    SELECTEDVALUE('Table'[stock_year])
)


     

    SelectedMonth =
IF(
    ISBLANK(SELECTEDVALUE('Table'[stock_month])),
    CALCULATE(MAX('Table'[stock_month]), FILTER('Table', 'Table'[stock_year] = [SelectedYear])),
    SELECTEDVALUE('Table'[stock_month])
)

     

    CALCULATE(
    [Value],
    'Table'[stock_year] = [SelectedYear],
    'Table'[stock_month] = [SelectedMonth]
)

     
    Please use the corresponding table names

  • sivarajan21's avatar
    sivarajan21
    Icon for Post Prodigy rankPost Prodigy
    1 year ago

    Hi JothiG 

     

    Simplest approach would be create a dax and use it in your chart:

    Total Amount Dynamic :=
VAR _SelYear =
    SELECTEDVALUE ( 'Date'[Year] )
VAR _SelMonth =
    SELECTEDVALUE ( 'Date'[Month] )
VAR _MaxYear =
    CALCULATE ( MAX ( 'Date'[Year] ), ALL ( 'Date' ) )
VAR _MaxMonth =
    CALCULATE (
        MAX ( 'Date'[Month] ),
        ALL ( 'Date' ),
        'Date'[Year] = _MaxYear
    )
RETURN
CALCULATE (
    [Total Amount],
    KEEPFILTERS (
        FILTER (
            ALL ( 'Date' ),
            'Date'[Year] = COALESCE ( _SelYear, _MaxYear )
                && 'Date'[Month] = COALESCE ( _SelMonth, _MaxMonth )
        )
    )
)

    Please let me know if it doesn't work!

    Second approach would be to create a field parameter to make it dynamic. First Try this and let me know

     

    Best Regards,