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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors