Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Goal :
Repeating "Target" in every quarter compare to quarterly "Actual" by apply "Date.Quarter" Axis in cluster chart.
Problem :
Able to achieving "Target (value only 1row in Q1) vs Actual (every rows) %" in scoreard . But cannot apply of line chart with "Shared azis" by [Date].[Quarter]
# Table name : "data"
Quarterly | Year | Target | Actual |
Q1 | 2020/01/01 | 900 | 200 |
Q2 | 2020/04/01 | 210 | |
Q3 | 2020/09/01 | 205 | |
Q4 | 2020/12/01 | 230 | |
Q1 | 2021/02/01 | 800 | 203 |
Q2 | 2021/06/01 | 214 | |
Q3 | 2021/08/01 | 220 | |
Q4 | 2021/12/01 | 255 |
Image :
DAX
Since REMOVEFILTERS() or VAR -- RETURN operation work against the filter of "Date.Quarter" Axis ,
no normal DAX expression I can find to manipulate the "Target" value to every quarter.
Or the only way is prepare another data table from scracth which duplibcate "Target" x 4 then match 4Q Date? (seems not productive )
Solved! Go to Solution.
@rane19 , Move target on last date of year nad you can use
CLOSINGBALANCEYEAR - as given in blog
Also this should work
This Year = CALCULATE(sum('Table'[Target]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
prefer separate date/qtr year table
@amitchandak appreciate a lot definitely it is a great method but my bad for didn't obviously explain that my Goal is letting the Year Target repeatdly show in every quarter .(Different from the idea of "disturbute") That you can imagine that 1 annual target show 4 times in the dataset of every quarter starting which quite illogical but that is my goal . I may have to process another separate dataset before import to Power BI which contain this illgoical shape to achieve the goal of this chart .
@rane19 , Move target on last date of year nad you can use
CLOSINGBALANCEYEAR - as given in blog
Also this should work
This Year = CALCULATE(sum('Table'[Target]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
prefer separate date/qtr year table
@rane19 , Seem like you have yearly target ,
Using date table you can distribute it in table or measure.
Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...
Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to...
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
86 | |
84 | |
52 | |
37 | |
23 |