Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
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).
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |