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 BI Gurus,
My Table looks like this:
Period | Scenario | Department | Revenue |
Dec-17 | Actual | department1 | 500 |
Mar-18 | Forecast | department1 | 550 |
Jun-18 | Forecast | department1 | 600 |
Sep-18 | Forecast | department1 | 500 |
Dec-18 | Forecast | department1 | 530 |
Dec-18 | Actual | department1 | 535 |
Mar-19 | Forecast | department1 | 580 |
Jun-19 | Forecast | department1 | 600 |
Sep-19 | Forecast | department1 | 620 |
Dec-19 | Forecast | department1 | 590 |
Dec-19 | Actual | department1 | 600 |
Mar-20 | Forecast | department1 | 620 |
Jun-20 | Forecast | department1 | 630 |
Sep-20 | Forecast | department1 | 620 |
Dec-20 | Forecast | department1 | 610 |
Dec-20 | Actual | department1 | 600 |
Dec-17 | Actual | department2 | 575 |
Mar-18 | Forecast | department2 | 633 |
Jun-18 | Forecast | department2 | 690 |
Sep-18 | Forecast | department2 | 575 |
Dec-18 | Forecast | department2 | 610 |
Dec-18 | Actual | department2 | 615 |
Mar-19 | Forecast | department2 | 667 |
Jun-19 | Forecast | department2 | 690 |
Sep-19 | Forecast | department2 | 713 |
Dec-19 | Forecast | department2 | 679 |
Dec-19 | Actual | department2 | 690 |
Mar-20 | Forecast | department2 | 713 |
Jun-20 | Forecast | department2 | 725 |
Sep-20 | Forecast | department2 | 713 |
Dec-20 | Forecast | department2 | 702 |
Dec-20 | Actual | department2 | 700 |
Dec-19 | Actual | department 3 | 345 |
Mar-20 | Forecast | department 3 | 357 |
Jun-20 | Forecast | department 3 | 362 |
Sep-20 | Forecast | department 3 | 357 |
Dec-20 | Forecast | department 3 | 351 |
Dec-20 | Actual | department 3 | 350 |
my goal is to get to this view so that Current year is picked from a slicer and prior year is dinamically calculated as curent year - 1
PY | Forecast03 | Forecast06 | Forecast09 | Forecast12 | CY | |
department1 | 600 | 620 | 630 | 620 | 610 | 600 |
department2 | 690 | 713 | 725 | 713 | 702 | 700 |
department 3 | 345 | 356.5 | 362.25 | 356.5 | 350.75 | 350 |
I was able to get to this with setting up a date table and using the year from the date table in the slicer
Here is what i don't know how to aproach:
per above data if current year is 2019- department 3 doesn't have any activity for prior year, the record for 2018 activity doesn't exist
2018 | 2019 | |||||
PY | Forecast03 | Forecast06 | Forecast09 | Forecast12 | CY | |
department1 | 535 | 580 | 600 | 620 | 590 | 600 |
department2 | 615 | 667 | 690 | 713 | 679 | 690 |
department 3 | 345 |
i want to exclude department 3 scenario from analyis . would you please advise? If prior year record for scenario= "Actual" doesn't exist or is 0 or is null - do not include department in the calc, however if i selected 2020 as my current year- i would need department 3 to show since it does have activity for 2019.
Thank you!
Olga
Solved! Go to Solution.
Hi @lbendlin ,
my CY Measure is
your last filter is meaningless
FILTER('Date','Date'[CurrentYear]='Date'[CurrentYear])
your measure seems to be missing a closing bracket.
Your measure should look similar to this:
CY=
var m = max('Date'[CurrentYear])
var p = CALCULATE(SUM('Table'[Revenue]), 'Table'[Scenario]="Actual", FILTER('Date','Date'[CurrentYear]=m-1) )
return if (ISBLANK(p),BLANK(),CALCULATE(SUM('Table'[Revenue]),'Table'[Scenario]="Actual"))
Remember that measures are computed for all elements of a visual - table cells, row and column subtotals, and the grand total. That means you have to consider up to four different computations depending on what you want to achieve.
I found that starting from the perspective of the grand total is the most effective way.
your last filter is meaningless
FILTER('Date','Date'[CurrentYear]='Date'[CurrentYear])
your measure seems to be missing a closing bracket.
Your measure should look similar to this:
CY=
var m = max('Date'[CurrentYear])
var p = CALCULATE(SUM('Table'[Revenue]), 'Table'[Scenario]="Actual", FILTER('Date','Date'[CurrentYear]=m-1) )
return if (ISBLANK(p),BLANK(),CALCULATE(SUM('Table'[Revenue]),'Table'[Scenario]="Actual"))
Thanks for the explanation, @lbendlin , this approach does work for dropping department 3 from the visual, but subtotal in the visual is still picking up department 3. is there any way to modify the formula so it subtotals only visible rows or creating separate mesuares and using Union to bring them in place of subtotals is my only choice?
All you need to do is BLANK() out measure CY for that scenario - then department 3 will disappear from the visual
Hi @lbendlin ,
my CY Measure is
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 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |