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 all,
even though there are various related & solved topics posted, I am not able to apply any of the solutions to my problem. Please point me in the right direction!
I have below data table and want to caluclate YTD (Planned Sales) based on the period selection by the user. If Period 7 is selected, sum all for periods 1-7, if 6 is selected sum all for periods 1-6.
There are other slicers available: Currency & Entity
Year | Period | Currency | Entity | Planned Sales | MonthEndDate | ||
2020 | 1 | EUR | A | 10 | 31-Jan-2020 | ||
2020 | 1 | MYR | A | 45 | 31-Jan-2020 | ||
2020 | 2 | EUR | A | 20 | 29-Feb-2020 | ||
2020 | 2 | MYR | A | 90 | 29-Feb-2020 | ||
2020 | 1 | EUR | B | 10 | 31-Jan-2020 | ||
2020 | 1 | CNY | B | 70 | 31-Jan-2020 | ||
2020 | 2 | EUR | B | 20 | 29-Feb-2020 | ||
2020 | 2 | CNY | B | 140 | 29-Feb-2020 | ||
I tried the following:
Solved! Go to Solution.
Hi @MelStaunton
You need to apply something that removes filters from the "Data" table when doing your CALCULATE (I named my table "Planned Sales")
Planned Sales YTD =
VAR MinDate =
DATE ( YEAR ( MAX ( 'Planned Sales'[MonthEndDate] ) ), 1, 1 )
VAR MaxDate =
SELECTEDVALUE ( 'Planned Sales'[MonthEndDate] )
RETURN
CALCULATE (
SUM ( 'Planned Sales'[Planned Sales] ),
FILTER (
ALLEXCEPT (
'Planned Sales',
'Planned Sales'[Currency],
'Planned Sales'[Entity]
),
'Planned Sales'[MonthEndDate] >= MinDate
&& 'Planned Sales'[MonthEndDate] <= MaxDate
)
)
You didn't say at what granularity you wanted the YTD total, so modify ALLEXCEPT as needed (or switch to ALL if no other filters are wanted).
All that being said, if you want to use Time Intelligence functions (TOTALYTD, etc), then the dates you reference should be from your date table, not the fact table.
Hope this helps
David
Hi @MelStaunton -
After looking through your sample report, you're going to need a separate date table to do these calculations instead of just trying to use the dates on the fact table. The advantage to this is that you can start using functions like TOTALYTD instead of having to "build" that function from scratch.
Also, just for the sake of completeness, on the donut chart you should use the "Dimension" industry instead of the industry value from the fact table. In neither case will this show a 0 on the donut chart itself, but all of the industries will show up on the legend.
I have attached your sample report with a second page that uses the DateTab date table to run the same calculations.
Hope this helps
David
Sorry @dedelman_clng
I didn't understand that the issue I have with the donut chart, is an underlying one which didn't show in the sample data (which I have amended).
Not every entity has an entry for each Industry every month. So while I theoretically get correct numbers with the below formula, only values that have an entry in the selected period actually show (if period 7 is selected, numbers add up correctly but only industries that have a value in period 7 show). I only noticed once I started filtering entities.....
Any ideas are much appreciated!
Planned Sales YTD Industry =
VAR MinDate =
DATE ( YEAR ( MAX ( 'POC Data'[MonthEndDate] ) ), 1, 1 )
VAR MaxDate =
SELECTEDVALUE ( 'POC Data'[MonthEndDate] )
RETURN
CALCULATE (
SUM ( 'POC Data'[Planned sales volume] ) / 1000,
FILTER (
ALLEXCEPT (
'POC Data',
'POC Data'[Currency],
'POC Data'[LE],
'POC Data'[ProjectType2],
'POC Data'[Project category]
),
'POC Data'[MonthEndDate] >= MinDate
&& 'POC Data'[MonthEndDate] <= MaxDate
),
GROUPBY ( 'POC Data', 'POC Data'[Customer Industry] )
)
Year | Period | Currency | Entity | Planned Sales | MonthEndDate | Industry | |
2020 | 1 | EUR | A | 10 | 31-Jan-2020 | Mobility | |
2020 | 1 | MYR | A | 45 | 31-Jan-2020 | Mobility | |
2020 | 2 | EUR | A | 20 | 29-Feb-2020 | Energy | |
2020 | 2 | MYR | A | 90 | 29-Feb-2020 | Energy | |
2020 | 1 | EUR | B | 10 | 31-Jan-2020 | Mobility | |
2020 | 1 | CNY | B | 70 | 31-Jan-2020 | Mobility | |
2020 | 2 | EUR | B | 20 | 29-Feb-2020 | Medical | |
2020 | 2 | CNY | B | 140 | 29-Feb-2020 | Medical |
@MelStaunton are you saying that you want an industry to be represented as 0 if it has no records in that period? For that you can put "+0" at the very tail end of your calculate code. ETA: Although I don't think that zeroes would show up on donut/pie charts - they would, however, on table/matrix and usually line charts.
If that's not what you are saying, can you please elaborate or mock up what you are trying to do with respect to industries?
David
Hi @MelStaunton -
After looking through your sample report, you're going to need a separate date table to do these calculations instead of just trying to use the dates on the fact table. The advantage to this is that you can start using functions like TOTALYTD instead of having to "build" that function from scratch.
Also, just for the sake of completeness, on the donut chart you should use the "Dimension" industry instead of the industry value from the fact table. In neither case will this show a 0 on the donut chart itself, but all of the industries will show up on the legend.
I have attached your sample report with a second page that uses the DateTab date table to run the same calculations.
Hope this helps
David
@MelStaunton , as long as the period is coming from the date table, you should be able to use datesytd /time intelligence with date table
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31")) // Changed based on FYLast YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
1.Creating Financial Calendar
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
Hi @MelStaunton
You need to apply something that removes filters from the "Data" table when doing your CALCULATE (I named my table "Planned Sales")
Planned Sales YTD =
VAR MinDate =
DATE ( YEAR ( MAX ( 'Planned Sales'[MonthEndDate] ) ), 1, 1 )
VAR MaxDate =
SELECTEDVALUE ( 'Planned Sales'[MonthEndDate] )
RETURN
CALCULATE (
SUM ( 'Planned Sales'[Planned Sales] ),
FILTER (
ALLEXCEPT (
'Planned Sales',
'Planned Sales'[Currency],
'Planned Sales'[Entity]
),
'Planned Sales'[MonthEndDate] >= MinDate
&& 'Planned Sales'[MonthEndDate] <= MaxDate
)
)
You didn't say at what granularity you wanted the YTD total, so modify ALLEXCEPT as needed (or switch to ALL if no other filters are wanted).
All that being said, if you want to use Time Intelligence functions (TOTALYTD, etc), then the dates you reference should be from your date table, not the fact table.
Hope this helps
David
@dedelman_clng Great, thank you! I adjusted my ALLEXCEPT and now get the correct values in a table.
Follow on question: I'd like to display those values grouped by "industry" and "cluster" in a donut chart. Where do I put the group by?
If I understand your followup question correctly, "industry" and "cluster" go in the Legend section of the donut chart. Planned Sales YTD is the Value.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |