Forum Discussion

balu810's avatar
balu810
Advocate I
3 years ago
Solved

Dynamic YTD calculation

I have data in below format , user wants single select slicer on Yearquarter , when he select 202202 , we have to show him two bars one for year 2021 and one for year 2022 . 2021 bar should show 202101,202102 value and 2022 bar should show 2022101 , 202202 value as a sum 

 

2021-300

2022-150

 

when user selects 202203 it should show all three quarters of 2022 and first three quarters of 2021.

 

YearquarterYearQuarterSales
202101202101100
202102202102200
202103202103300
202104202104400

202201

202201120
202202202202130
202203202203140
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

     

     

     

     

    expected result measure: =
VAR _sliceryear =
    MAX ( 'Calendar'[Year] )
VAR _prevsliceryear = _sliceryear - 1
VAR _slicerquarter =
    MAX ( 'Calendar'[Quarter] )
RETURN
    SUMX (
        FILTER (
            Data,
            Data[Year]
                IN { _sliceryear, _prevsliceryear }
                && Data[Quarter] <= _slicerquarter
        ),
        Data[Sales]
    )

     

2 Replies