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.
Hi,
I'm trying to create a line graph to show 2 values, with month as axis
1. accumulated value per month in current year
2. accumulated value from the first date of transaction (could be more than a year ago) until the end of previous year to be shown constant to all month in the graph
Note: these 2 values must be able to be filtered by month & other categories.
I have a difficulty in applying a correct formula for value #2 that should not be affected by the month filter
I cannot use ALL function, considering the Note above.
anyone can help me what formula I should use?
below is the graph I'm looking for.
Really appreciate help.
Solved! Go to Solution.
Hi @RMV,
Share the link from where i can download your file.
Hi @Anonymous, I need the previous year figure not to be affected by the month filter applied. Only the current year figure is affected by the month filter.
@Ashish_Mathur, sorry I cannot share my pbix here.
I provide some data samples below, though.
The raw data sample is as follow
Entity | Date | Category 1 | Category 2 | Amount |
1 | 30-Apr-15 | A1 | B1 | 1000 |
1 | 4-Jun-15 | A1 | B2 | 200 |
2 | 25-Sep-15 | A1 | B1 | 57 |
2 | 8-Dec-15 | A2 | B1 | 60 |
1 | 3-Feb-16 | A1 | B2 | 500 |
1 | 9-Aug-16 | A2 | B1 | 700 |
2 | 31-Oct-16 | A1 | B1 | 45 |
2 | 4-Nov-16 | A1 | B2 | 70 |
1 | 11-Jan-17 | A2 | B1 | 1200 |
1 | 22-Feb-17 | A1 | B1 | 1300 |
And the result expected, with only month filter applied is as follow
Entity All Category 1 All
Month Jan-Feb Category 2 All
Current Year | Previous Year | |
Jan | 3832 | 2632 |
Feb | 5132 | 2632 |
Where, Jan previous year is calculated from all 2016 values and before (1000 + 200 + 57 + 60 + 500 + 700 + 45 + 70)
Feb previous year is calculated the same as Jan previous year values
Jan current year is calculated from Jan 2017 values and before (1000 + 200 + 57 + 60 + 500 + 700 + 45 + 70 + 1200)
Feb current year is calculated from Feb 2017 values and before (1000 + 200 + 57 + 60 + 500 + 700 + 45 + 70 + 1200 + 1300)
When some other filters applied, the result expected is as follow
Entity 1 Category 1 All
Month Jan Category 2 B1
Current Year | Previous Year | |
Jan | 2200 | 1700 |
Feb | 1700 |
Jan Previous Year is calculated with 2016 values and before for Entity 1 & Category 2 = B1 (1000 + 700)
Feb Previous Year is calculated the same as Jan Previous Year
Jan Current Year is calculated with Jan 2017 values and before for Entity 1 & Category 2 = B1 (1000 + 700 + 1200)
Feb Current Year is showing blank, since the month is filtered to Jan only
You are welcome. If my reply helped, please mark it as Answer.
Try this:
Cumulative For Last Year = VAR StartingMonth = MIN(Calendar[Month]) VAR EndingMonth = MAX(Calendar[Month]) VAR CurrentYear = MAX(Calendar[Year]) VAR LastYear = CurrentYear - 1 RETURN CALCULATE( [Sum Measure], Filter( ALL(Calendar), Calendar[Year] = LastYear && Calendar[Month] >= StartingMonth && Calendar[Month] <= EndingMonth ) )
This will listen to the month slicer and return all months within the the selected range, but only for last year.
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |