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,
I am currently working on updating a dashboard I build with data for the last quarter. So far it had included only data for one year (2021) and now that there is another year, one of my calculations stopped working. The data looks like this:
periodID | scenario | value | date |
202112 | PL | 10166298222 | 20211201 |
202203 | PL | 10182400343 | 20220301 |
202206 | PL | 10116872698 | 20220601 |
202212 | PL | 10517026694 | 20221201 |
This table is connected to the date table via the date field in a 1 to * relationship (calendar being 1, the table also has data for other scenarios than PL for the same periods).
In my visual I need to always display the value for the last period of the current year. To do this I use the following calculation:
CALCULATE(SUM('table'[value]), 'table'[scenario] = "PL", ALLEXCEPT('Calendar','Calendar'[Year]), 'Calendar'[Month]=12)
The whole page is filtered to period 202203 (March 2022) but when I use my measure in a visual I always get data for both 202112 and 202212 despite the page filter being set to 2022 as evidenced by the Year column:
Shouldn't ALLEXCEPT keep the Year filter intact for the calculation?
Solved! Go to Solution.
Hi @yamayancha ,
Please update the formula of your measure as below and check whether it can get your desired result...
Measure = CALCULATE ( SUM ( 'table'[value] ), FILTER ( 'table', 'table'[scenario] = "PL" && YEAR ( 'table'[date] ) = SELECTEDVALUE ( 'Calendar'[Year] ) && MONTH ( 'table'[date] ) = 12 ) ) |
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
@yamayancha , try like
CALCULATE(SUM('table'[value]), 'table'[scenario] = "PL", Filter(all('Calendar'), 'Calendar'[Month]=12))
or
CALCULATE(SUM('table'[value]), 'table'[scenario] = "PL", Filter(all('Calendar''[Month]), 'Calendar'[Month]=12))
Thank you for your reply.
The first version FILTER(ALL('Calendar')) gives me a similar result to what I had before except that the 2021 value is now also filled outside of the total column:
The second version FILTER(all('Calendar'[Month]) results in an empty table:
Hi @yamayancha ,
Please update the formula of your measure as below and check whether it can get your desired result...
Measure = CALCULATE ( SUM ( 'table'[value] ), FILTER ( 'table', 'table'[scenario] = "PL" && YEAR ( 'table'[date] ) = SELECTEDVALUE ( 'Calendar'[Year] ) && MONTH ( 'table'[date] ) = 12 ) ) |
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
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 |
---|---|
113 | |
103 | |
77 | |
66 | |
63 |
User | Count |
---|---|
142 | |
105 | |
102 | |
81 | |
68 |