Summing multiple columns based on dynamic filter

[Anonymous]

Lets say I have the following dataset:

Project	January	February	March	April	May	June	YTD
A	12	23	155	23	45	12	?
B	53	43	25	9	10	10	?

I would like to add a YTD column that adds up all the months data up to the date selected via a slicer. For example, if April was selected on the slicer, then the YTD columns for Project A would be (12 + 23 + 155 + 23 = 213). How would I go about doing this? 

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Have you solved this problem? If not, you may take steps below for reference.

1. For more convenient operation, you can transpose the table first.

Hit Use First Row as Headers, then

2. Create a calendar table

Calendar Table = SUMMARIZE('Table','Table'[Project])

Keep them disconnected：

3. Create the three measures：

sel month = SWITCH (
    MAX('Table'[Project]),
    "January",1,
    "February",2,
    "March",3,
    "April",4,
    "May",5,
    "June",6
)

YTD_A =
VAR slicer_month =
    SWITCH (
        MAX ( 'Calendar Table'[Project] ),
        "January", 1,
        "February", 2,
        "March", 3,
        "April", 4,
        "May", 5,
        "June", 6
    )
VAR total =
    CALCULATE (
        SUM ( 'Table'[A] ),
        KEEPFILTERS ( FILTER ( ALL ( 'Table' ), 'Table'[sel month] <= slicer_month ) )
    )
RETURN
    IF ( HASONEVALUE ( 'Table'[Project] ), SELECTEDVALUE ( 'Table'[A] ), total )

YTD_B =
VAR slicer_month =
    SWITCH (
        MAX ( 'Calendar Table'[Project] ),
        "January", 1,
        "February", 2,
        "March", 3,
        "April", 4,
        "May", 5,
        "June", 6
    )
VAR total =
    CALCULATE (
        SUM ( 'Table'[B] ),
        KEEPFILTERS ( FILTER ( ALL ( 'Table' ), 'Table'[sel month] <= slicer_month ) )
    )
RETURN
    IF ( HASONEVALUE ( 'Table'[Project] ), SELECTEDVALUE ( 'Table'[B] ), total )

4.then,

Result:

Hope this helps.

Best Regards,

Community Support Team _ Tang

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

[Jihwan_Kim]

hi, @Anonymous 

I am not sure how your actual data model looks like, but I suggest having the fact table's structure like below.

You can easily transform the table structure in Power Query Editor.

And also, a separate slicer table is needed in this case.

All measures are in the sample pbix file. (Link down below).

Values Total YTD =

VAR slicerdate =

MAX ( Slicer[Date] )

RETURN

CALCULATE (

TOTALYTD ( [Values Total], 'Calendar'[Date] ),

KEEPFILTERS ( FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= slicerdate ) )

)

https://www.dropbox.com/s/wlvg6ktc982o4sc/bogrowr.pbix?dl=0 

Hi, My name is Jihwan Kim.

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

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM