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.
I currently have my quarterly targer set at 6. For me to get a daily target value in the case I selected a particular date, it gives me what I want. However I need the target value to reset every quarter. Below is what I have as my solution
Assuming I have a fixed expected quarterly target of 6.
This is how I want the table broken down if there is a date selection with their respective quarters.
Quarter | Date | Target |
Q1 2022 | 1/7/2022 | 0.47 |
Q1 2022 | 3/25/2022 | 5.67 |
Q1 2022 | 3/31/2022 | 6.00 |
Q2 2022 | 4/8/2022 | 0.67 |
However, When I have it selected as a quarter, I get the numbers compounded like this. I get 6.07 when I select just Q2 2022 since it is taking 1st day of Q2 2022 which is 4/1/2022.
Quarter | Target |
Q1 2022 | 6.00 |
Q2 2022 | 6.07 |
Expected Result: I want the target valies to reset every quarter so that it compounds to 6 by the end of every quarter.
Quarter | Target |
Q1 2022 | 6.00 |
Q2 2022 | 0.07 |
Thank you for your help.
Solved! Go to Solution.
Hi @yve214
You can try
Target =
VAR LastDateInFilter =
MAX ( 'Date'[Date] )
VAR FirstDayInQuarter =
STARTOFQUARTER ('Date'[Date] )
VAR LastDayInQuarter =
ENDOFQUARTER ('Date'[Date] )
VAR DailyTarget =
DIVIDE (
6,
DATEDIFF ( FirstDayInQuarter, LastDayInQuarter, DAY )
)
VAR Result =
DailyTarget * DATEDIFF ( FirstDayInQuarter, LastDateInFilter, DAY )
RETURN
Result
Hi @yve214
You can try
Target =
VAR LastDateInFilter =
MAX ( 'Date'[Date] )
VAR FirstDayInQuarter =
STARTOFQUARTER ('Date'[Date] )
VAR LastDayInQuarter =
ENDOFQUARTER ('Date'[Date] )
VAR DailyTarget =
DIVIDE (
6,
DATEDIFF ( FirstDayInQuarter, LastDayInQuarter, DAY )
)
VAR Result =
DailyTarget * DATEDIFF ( FirstDayInQuarter, LastDateInFilter, DAY )
RETURN
Result
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 |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |