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.
Hello Community,
I have a sample dataset given below, where, I want to filter by year and calculate the total amount for each id for current year and past year.
ImpYear | ReportedDate | Id | Amount |
2017 | 01/01/2017 | 1 | 10 |
2017 | 01/01/2017 | 2 | 7 |
2017 | 01/01/2017 | 3 | 9 |
2018 | 01/01/2018 | 1 | 23 |
2018 | 01/01/2018 | 2 | 15 |
2018 | 01/01/2018 | 3 | 19 |
2019 | 01/01/2019 | 1 | 12 |
2019 | 01/01/2019 | 2 | 17 |
2019 | 01/01/2019 | 3 | 18 |
The final output should have 'Year' present in the filter so that I can select any year from my data. Below is my expected output if I have filtered out Year to 2019:
Id | Current Year Total | Past Year Total |
1 | 12 | 23 |
2 | 17 | 15 |
3 | 18 | 19 |
Also please note that the 'ReportedDate' column is actually a hierarchy column with day, month, quarter and year where I have consirdered only the 'year' part.
Solved! Go to Solution.
Hi @jazzk ,
There is option to create disconnected table.
Example: create new table which will have values 2017, 2018, 2019. This table must not have any connection to existing table where you have measures.
In slicer use Year from this new disconnected table.
Now create these 2 measures:
Regards,
Nemanja Andic
Hi @jazzk , attached power bi file with both scenarios.
One scenario (on the right) which is simpler, it doesn't use disconnected table so everything is based on original (regular) table.
Second scenario (on the left) if you are using disconnected table.
Regards,
Nemanja Andic
@nandic I want a Last YTD total which is different from Last Year total. So in order to do that, I tried this query which is not giving any value:
Last YTD =
@jazzk , attached new version of the file, i missed that you wrote last year "ytd".
New version of the file is focused on regular measures (i deleted disconnected table and measures).
@nandic Since I have data for new months loaded, it seems that the Past YTD does not calculate the right amount. For example, currently it's March and ideally the formula should have added the amounts for the month of January, February and March of previous year, however, it only displays the amount for January. Could you please help me fix this?
Hi @jazzk ,
It was my mistake, i have updated the formula and data in the file so you can check it.
Formula:
Past YTD v2 =
VAR _currentYear =
MAX ( 'Table'[ImpYear] ) - 1 -- get last year
VAR _TodayDate =
TODAY () -- today date
VAR _CriteriaDate =
DATE ( _currentYear, MONTH ( _TodayDate ), DAY ( _TodayDate ) ) -- calculate same day previous year
RETURN
CALCULATE (
[Current Year],
'Table'[ImpYear] = _currentYear,
'Table'[ReportedDate] <= _CriteriaDate
)
If you select 2020, measure "Past YTD v2" will calculate amount for period 1. Jan 2019 - 2. March 2019.
Regards,
Nemanja Andic
Hello @wdx223_Daniel The above measure that you have created is Past Year Total. How would I calculate Current Year Total?
sum(table[amount]
Hi @jazzk ,
There is option to create disconnected table.
Example: create new table which will have values 2017, 2018, 2019. This table must not have any connection to existing table where you have measures.
In slicer use Year from this new disconnected table.
Now create these 2 measures:
Regards,
Nemanja Andic
Hello @nandic ,
Thank you for your response! I also wanted to calculate the 'Change YTD', which also be based on the year that will be chosen from the filter. My current DAX query looks like:
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |