cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
balu810
Frequent Visitor

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
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

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

 

Jihwan_Kim_1-1669360343695.png

 

Jihwan_Kim_0-1669360292436.png

 

 

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]
    )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

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

 

Jihwan_Kim_1-1669360343695.png

 

Jihwan_Kim_0-1669360292436.png

 

 

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]
    )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


balu810
Frequent Visitor

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors