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,
Pretty new user of Power BI here and faces some difficulties as per listed below.
We really hope that someone would gives advice/help on this.
As we are trying to make comparisons between sales performance of pervious year vs this year by Period, we definitely need “Date Intelligence” through “Quick Measures” in power Bi which could provide a great help on this calculation.
However, the date intelligence is missing which could be due no date hierarchy is available as well.
Example of raw data:
Date | Year | Qtr | Period | Customer | Sales |
1/12/2018 | 2018 | 1 | 1 | A | 100 |
5/12/2019 | 2019 | 1 | 1 | A | 200 |
5/2/2018 | 2018 | 1 | 3 | B | 400 |
5/2/2018 | 2018 | 1 | 3 | B | 400 |
5/2/2019 | 2019 | 1 | 3 | B | 300 |
** Date column is not linked to any other tables
** Data type has already set to Date/Time
** Both date intelligence and date hierarchy are available automatically when we created a new table with a date column.
Note that we are trying to make a comparisons between pervious year vs this year from summarized data by Year, Period and Customer as per below desired output:
Year | Qtr | Period | Customer | Sum of Sales | Growth% |
2018 | 1 | 1 | A | 100 |
|
2018 | 1 | 3 | B | 800 |
|
2019 | 1 | 1 | A | 200 | 100% |
2019 | 1 | 3 | B | 300 | -63% |
Thank you.
Solved! Go to Solution.
Hi @JOO13
Create three measures
total value = CALCULATE ( SUM ( Table1[Sales ] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Year] = MAX ( Table1[Year] ) && Table1[Period] = MAX ( Table1[Period] ) && Table1[Customer ] = MAX ( Table1[Customer ] ) ) ) LY = CALCULATE ( [total value], FILTER ( ALLSELECTED ( Table1 ), Table1[Year] = MAX ( Table1[Year] ) - 1 && Table1[Period] = MAX ( Table1[Period] ) && Table1[Customer ] = MAX ( Table1[Customer ] ) ) ) growth = IF([LY]<>BLANK(),([total value]-[LY])/[LY])
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you! This is definitely useful but we are interested to understand why both the Date Hierarchy and Date Intelligence are missing as well. I figured out that it is due to we used "Direct Query" instead of "Import" when we are retrieving the data from the database. Both Date Hierarchy and Date Intelligence are available only when the raw data are retrieved through "Import".
Hi @JOO13
Create three measures
total value = CALCULATE ( SUM ( Table1[Sales ] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Year] = MAX ( Table1[Year] ) && Table1[Period] = MAX ( Table1[Period] ) && Table1[Customer ] = MAX ( Table1[Customer ] ) ) ) LY = CALCULATE ( [total value], FILTER ( ALLSELECTED ( Table1 ), Table1[Year] = MAX ( Table1[Year] ) - 1 && Table1[Period] = MAX ( Table1[Period] ) && Table1[Customer ] = MAX ( Table1[Customer ] ) ) ) growth = IF([LY]<>BLANK(),([total value]-[LY])/[LY])
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you! This is definitely useful but we are interested to understand why both the Date Hierarchy and Date Intelligence are missing as well. I figured out that it is due to we used "Direct Query" instead of "Import" when we are retrieving the data from the database. Both Date Hierarchy and Date Intelligence are available only when the raw data are retrieved through "Import".
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 |
---|---|
115 | |
100 | |
88 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |