Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
yve214
Helper III
Helper III

Quarterly Target to reset every quarter

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.

QuarterDateTarget
Q1 20221/7/20220.47
Q1 20223/25/20225.67
Q1 20223/31/20226.00
Q2 20224/8/20220.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.

QuarterTarget
Q1 20226.00
Q2 20226.07

 

Expected Result: I want the target valies to reset every quarter so that it compounds to 6 by the end of every quarter.

QuarterTarget
Q1 20226.00
Q2 20220.07

 

Thank you for your help.

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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 

 

1.png

View solution in original post

2 REPLIES 2
yve214
Helper III
Helper III

@tamerj1 , Thank you so much, this helped alot!

tamerj1
Super User
Super User

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 

 

1.png

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors