Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Linear pacing calculation for the current Quarter

Hey guys, 

 

I'd like to create a simple calculation that would spit out one number (%) based on the current Quarter we are in right now. 

I call it Pacing%.

 

No. of Days in the current Quarter - (calculate how many days we have in the quarter we are in right now. Currently we are still in Q1 so that is 90)

Days-in within current Quarter - (On which day number are we on currently in the quarter. Currently we are on day 90 in Q1)

 

Pacing% = Days-in within current Quarter / No. of Days in the current Quarter

This currently would be 100% (90 / 90)

But as a better example if Today is March 11 then Pacing% = 78% (70/90)

 

I would reference this percentage in other calculations (in Measures). Can you help me how I could calculate this?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Firstly, there should be a DimDate table in your data model.

    DimDate = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 01, 01 ), DATE ( 2022, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Qtr", QUARTER ( [Date] ),
    "Month", MONTH ( [Date] )
)

    Then try this code to calcualte [Pacing%] by measure.

    Pacing % = 
VAR _Today =
    DATE ( 2022, 03, 11 ) /* Today()*/
VAR _Year =
    YEAR ( _Today )
VAR _Qtr =
    QUARTER ( _Today )
VAR _No_of_Days_in_the_current_Quarter =
    CALCULATE (
        COUNTROWS ( DimDate ),
        FILTER ( ALL ( DimDate ), DimDate[Year] = _Year && DimDate[Qtr] = _Qtr )
    )
VAR _Qtr_Begin =
    CALCULATE (
        MIN ( DimDate[Date] ),
        FILTER ( ALL ( DimDate ), DimDate[Year] = _Year && DimDate[Qtr] = _Qtr )
    )
VAR _Days_in_within_current_Quarter =
    DATEDIFF ( _Qtr_Begin, _Today, DAY )
RETURN
    DIVIDE ( _Days_in_within_current_Quarter, _No_of_Days_in_the_current_Quarter )

    In my code I use 2022/03/11 directly, if you want to get today's date, you can try Today() function. 

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Can have Qtr Start Date and Qtr End Date

     

    Qtr Year = year([Date]) & [Qtr]
    Qtr Rank = RANKX(all('Date'),'Date'[Qtr Year ],,ASC,Dense)
    Qtr Start Date = minx(filter('Date', [Qtr Year] = earlier([Qtr Year])),[Date])

    Qtr End Date = maxx(filter('Date', [Qtr Year] = earlier([Qtr Year])),[Date])
    Qtr Day = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],Day)+1

     

    dates in Qtr

    datediff(Min('Date'[Qtr Start Date]), Max('Date'[Qtr End Date]), Day) +1

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Firstly, there should be a DimDate table in your data model.

    DimDate = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 01, 01 ), DATE ( 2022, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Qtr", QUARTER ( [Date] ),
    "Month", MONTH ( [Date] )
)

    Then try this code to calcualte [Pacing%] by measure.

    Pacing % = 
VAR _Today =
    DATE ( 2022, 03, 11 ) /* Today()*/
VAR _Year =
    YEAR ( _Today )
VAR _Qtr =
    QUARTER ( _Today )
VAR _No_of_Days_in_the_current_Quarter =
    CALCULATE (
        COUNTROWS ( DimDate ),
        FILTER ( ALL ( DimDate ), DimDate[Year] = _Year && DimDate[Qtr] = _Qtr )
    )
VAR _Qtr_Begin =
    CALCULATE (
        MIN ( DimDate[Date] ),
        FILTER ( ALL ( DimDate ), DimDate[Year] = _Year && DimDate[Qtr] = _Qtr )
    )
VAR _Days_in_within_current_Quarter =
    DATEDIFF ( _Qtr_Begin, _Today, DAY )
RETURN
    DIVIDE ( _Days_in_within_current_Quarter, _No_of_Days_in_the_current_Quarter )

    In my code I use 2022/03/11 directly, if you want to get today's date, you can try Today() function. 

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.