Need to change Divisor depending on if row shows a Quarterly or Monthly Total

[Roseventura]

Hi, 

I'm having trouble with my DAX.  I'm trying to calculate a Run Rate based on which fiscal day in the month or day in the quarter it is.  Everything looks correct except that the Q1 run rate is dividing based on the Selected Day in the Year-Mo for the last month in that quarter.  Here's my table:

There are 3 conditions to determine the run rate:

1. If the Year-Qtr is displayed, take Total Bookings $ and divide by the "Selected Day in Year-Qtr" value (61 for 2025-Q1).
2. If the Year-Mo is displayed, take Total Bookings $ and divide by the "Selected Day in Year-Mo" values (16 for 2025-01, 20 for 2025-02, and 25 for 2025-03).
3. If the Year-Mo and the Year-Qtr are current, use the "Day in Quarter Yesterday" value (8 representing 8th day in the current fiscal month and quarter).

Everything in the Total Booking RR column is correct except the 2025-Q1 value.  That is dividing 9,311 by 25, not 61.  The run rate for Q1 should be 152.64

Can someone assist me with trying to figure out how to switch the divisor values based on if it's showing Year-Qtr or Year-Mo?

Here are my DAX measures:

Total Booking RR =

VAR SelectDIYrMo = [Selected Day in Year-Mo]    >>> see below

VAR DIQYesterday = [Day in Quarter Yesterday]   >>> see below

VAR TodaysYQ = LOOKUPVALUE(Dates[YEAR-QTR], Dates[PADDATE], TODAY() )

VAR FindYrQtr = if(TodaysYQ = SELECTEDVALUE(Dates[YEAR-QTR]), DIQYesterday, SelectDIYrMo )

VAR Calc = divide ([Total Booking $] , FindYrQtr, 0 )

RETURN

Calc

Selected Day in Year-Mo = lookupvalue(Dates[CADIM],Dates[PADDATE], [Max Date])

Day in Quarter Yesterday = lookupvalue(Dates[CADIQ],Dates[PADDATE],  Today()-1 )

Max Date = max(Dates[PADDATE])

(PADDATE = date)

My DATE table has column which store the day in the month (CADIM) and the day in the quarter (CADIQ).

Any assistance would be great appreciated.

[v-priyankata · Accepted solution]

Hi @Roseventura,

We are grateful for your participation in the Microsoft Fabric Community forum.

As per my understanding of your question, please try the DAX measure below — it may help you:

Total Booking RR =
VAR MaxDate = MAX(Dates[PADDATE])
VAR SelectDIYrMo = LOOKUPVALUE(Dates[CADIM], Dates[PADDATE], MaxDate)
VAR SelectDIYrQtr = LOOKUPVALUE(Dates[CADIQ], Dates[PADDATE], MaxDate)
VAR DIQYesterday = LOOKUPVALUE(Dates[CADIQ], Dates[PADDATE], TODAY() - 1)
VAR TodaysYQ = LOOKUPVALUE(Dates[YEAR-QTR], Dates[PADDATE], TODAY())
VAR CurrentYQ = SELECTEDVALUE(Dates[YEAR-QTR])
VAR CurrentYM = SELECTEDVALUE(Dates[YEAR-MO])
VAR IsQuarterRow = ISBLANK(CurrentYM)

VAR FindYrQtr =
IF(
CurrentYQ = TodaysYQ,
DIQYesterday,
IF(IsQuarterRow, SelectDIYrQtr, SelectDIYrMo)
)

VAR Calc = DIVIDE([Total Booking $], FindYrQtr, 0)
RETURN
Calc

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[Roseventura]

Thank you!  That tweak is what did it!  Thank you so much for the quick answer!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...