topic Re: Facing challenge to calculate previous year QTD in DAX Commands and Tips

Facing challenge to calculate previous year QTD

Soumeli — Thu, 30 Apr 2026 14:38:07 GMT

Hi Team,

While converting BO to PBI, I am getting incorrect result for previous year QTD.

BO expression for QTD:

(Floor((DaysBetween(Min([QTD].[NAP Reporting Date]); Max([QTD].[NAP Reporting Date]))) - Truncate((DayNumberOfWeek(Min([QTD].[NAP Reporting Date])) + DaysBetween(Min([QTD].[NAP Reporting Date]); Max([QTD].[NAP Reporting Date]))) /7 ;0)*2)+0)Where ([QTD].[NAP Reporting Date]=2025)

My code in Power BI:

CALCULATE(

INT(

(

DATEDIFF(

Min([QTD].[NAP Reporting Date]),

Max([QTD].[NAP Reporting Date]),

Day

)

TRUNC(

(

WEEKDAY(Min([QTD].[NAP Reporting Date]),1) +

DATEDIFF(

Min([QTD].[NAP Reporting Date]),

Max([QTD].[NAP Reporting Date]),

Day

)

) / 7,

) * 2

)

QTD[nap_year]=YEAR(TODAY()) - 1

)

Max([QTD].[NAP Reporting Date]) is 04/29/2026. BO is returning 21 as PY QTD while power bi is returning 64. We need to exclude weekends and holidays. Please help me understand if there is anything wrong in my expression. Thanks in advance.

Re: Facing challenge to calculate previous year QTD

Soumeli — Thu, 30 Apr 2026 14:45:09 GMT

Hi Team,

While converting a report from BO to PBI, I am getting incorrect result for previous year QTD.

BO expression for QTD:

My code in Power BI:

CALCULATE(

INT(

(

DATEDIFF(

Min([QTD].[NAP Reporting Date]),

Max([QTD].[NAP Reporting Date]),

Day

)

TRUNC(

(

WEEKDAY(Min([QTD].[NAP Reporting Date]),1) +

DATEDIFF(

Min([QTD].[NAP Reporting Date]),

Max([QTD].[NAP Reporting Date]),

Day

)

) / 7,

) * 2

)

QTD[nap_year]=YEAR(TODAY()) - 1

)

Re: Facing challenge to calculate previous year QTD

Natarajan_M — Thu, 30 Apr 2026 19:02:22 GMT

Hi Soumeli , The setup of the calendar table plays a crucial role in this example. I have used a separate table to capture holiday details. Alternatively, you can create a flag column within the calendar table to indicate whether a day is a holiday or a workday.

Sample OP :

Total days : 29
Total Holidays : 2
Total working days : 21 (Mon to Fri)

data model :

Data :

DAX Code:

PY QTD Working Days = VAR TodayDate = MAX( 'Calendar'[NAP Reporting Date] ) VAR QStart = DATE(YEAR(TodayDate),(INT((MONTH(TodayDate)-1)/3)*3)+1,1) VAR PY_QStart = DATE(YEAR(QStart)-1, MONTH(QStart), 1) VAR PY_QEnd = DATE(YEAR(TodayDate)-1, MONTH(TodayDate), DAY(TodayDate)) VAR TotalDays = DATEDIFF(PY_QStart, PY_QEnd, DAY)+1 VAR StartWkDay = WEEKDAY(PY_QStart, 2) -- Monday=1 VAR WeekendDays= INT((StartWkDay + TotalDays) / 7) * 2 RETURN TotalDays - WeekendDays

PY QTD Working Days (Adj) = VAR TodayDate = MAX( 'Calendar'[NAP Reporting Date] ) VAR QStart = DATE(YEAR(TodayDate),(INT((MONTH(TodayDate)-1)/3)*3)+1,1) VAR PY_QStart = DATE(YEAR(QStart)-1, MONTH(QStart), 1) VAR PY_QEnd = DATE(YEAR(TodayDate)-1, MONTH(TodayDate), DAY(TodayDate)) VAR TotalDays = DATEDIFF(PY_QStart, PY_QEnd, DAY)+1 VAR StartWkDay = WEEKDAY(PY_QStart, 2) VAR WeekendDays= INT((StartWkDay + TotalDays) / 7) * 2 VAR HolidayCnt = CALCULATE( COUNTROWS(Holidays), Holidays[HolidayDate] >= PY_QStart, Holidays[HolidayDate] <= PY_QEnd, WEEKDAY(Holidays[HolidayDate],2) <= 5 ) RETURN TotalDays - WeekendDays - HolidayCnt

PBIX file :
Day Calc.pbix

Thanks
If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

For more Power BI tips and discussions, let’s connect on LinkedIn:
https://www.linkedin.com/in/natarajan-manivasagan

Cheers!

Re: Facing challenge to calculate previous year QTD

pcoley — Sun, 03 May 2026 03:26:44 GMT

I understand you're facing challenges with QTD calculations when converting from BO to Power BI, especially for previous year comparisons. The DAX `DATEDIFF` and `WEEKDAY` functions work slightly differently.

Here are two approaches:

Solution 1: Using `DATEDIFF` and adjusted `WEEKDAY` logic:

VAR MinDate = MIN ( 'QTD'[NAP Reporting Date] ) VAR MaxDate = MAX ( 'QTD'[NAP Reporting Date] ) VAR DaysInPeriod = DATEDIFF ( MinDate, MaxDate, DAY ) VAR StartDayOfWeek = WEEKDAY ( MinDate, 2 ) // Monday is 1, Sunday is 7 VAR WeeksCounted = TRUNC ( ( StartDayOfWeek + DaysInPeriod - 1 ) / 7, 0 ) VAR QTDDays = DaysInPeriod - WeeksCounted * 2 RETURN INT ( QTDDays )

Solution 2: Leveraging Time Intelligence for Previous Year QTD:

For previous year QTD, it's generally more robust to use DAX time intelligence functions. Create a date table and a measure like this:

Previous Year QTD = CALCULATE ( [Your Current QTD Measure], // Replace with your QTD measure SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Remember to replace `[Your Current QTD Measure]` and `'Date'[Date]` with your actual measure and date table column.

Re: Facing challenge to calculate previous year QTD

Soumeli — Sun, 03 May 2026 14:48:14 GMT

Hi @Natarajan_M

Thanks for your reply. I am very new in power bi. I couldn't edit query for calendar table as I can't log in.Can you please help me how you set up calendar table?Just using CALENDAR or CALENDARAUTO function will help?What about to use a separate table to capture holidays?

Re: Facing challenge to calculate previous year QTD

Soumeli — Sun, 03 May 2026 14:46:56 GMT

Hi pcoley

Thanks for your answer. Solution 1 didn't work for my case. It returned 281.

Re: Facing challenge to calculate previous year QTD

RDewi — Sun, 03 May 2026 19:30:02 GMT

Hi,

Try the following:

PrevYear_QTD =

VAR DatesTablePYQTD =

SAMEPERIODLASTYEAR ( DATESQTD ( DIM_YourCalendarTable[Date] ) ) --on the right side of your calendar date pls click 3 dots ... and select mark as Date table to use this function

RETURN

COUNTROWS (

FILTER (

DatesTablePYQTD,

WEEKDAY ( DIM_YourCalendarTable[Date], 2 ) <= 5 --Limit the days from Mon - Fri

)

Re: Facing challenge to calculate previous year QTD

FBergamaschi — Mon, 04 May 2026 11:51:07 GMT

Hi Soumeli

it seems to me you did not get an answer on how to setup a Dates table, here a simple script in DAX

Create a new table (Modeling -> New table) with this code and then connect it to the fact tables

Date =

VAR Union_Dates =

SELECTCOLUMNS (

DISTINCT (

UNION(

ALLNOBLANKROW ( Fact1[DateCol] ),

ALLNOBLANKROW ( Fact2[DateCol] ),

ALLNOBLANKROW ( FactN[DateCol] )

)

"@Date", Fact1[DateCol]

)

VAR Min_Date = MINX ( UNION_Dates, [@Date] )

VAR Max_Date = MAXX ( UNION_Dates, [@Date] )

VAR Min_Date_Year = YEAR ( Min_Date )

VAR Max_Date_Year = YEAR ( Max_Date )

VAR Begin_Cal = DATE ( Min_Date_Year, 1, 1 )

VAR End_Cal = DATE ( Max_Date_Year, 12, 31 )

VAR Calendar_base = CALENDAR ( Begin_Cal, End_Cal )

VAR Full_Calendar =

ADDCOLUMNS(

Calendar_base,

"Date For Filters", [Date],

"Year", YEAR ( [Date] ),

"Month", MONTH ( [Date] ),

"Month Name", FORMAT( [Date], "mmmm" ),

"Qrt","Qrt " & QUARTER ( [Date] ),

"WeekDay Nr", WEEKDAY( [Date], 2),

"WeekDay Name", FORMAT( [Date],"dddd" ),

"Week","Week " & WEEKNUM( [Date], 2 ),

"YearMonth", YEAR([Date]) & FORMAT( MONTH( [Date] ),"00" ),

"WorkingDay", IF ( WEEKDAY( [Date], 2) IN {6, 7}, "N", "Y" ),

"Past-Present", IF ( [Date] <= Max_Date, "Y", "N")

"YearMonthNr", [Year] * 12 + [Month]

)

RETURN

Full_Calendar

On the question: how about an additional table for holidays? Yes, that's a good idea. It should be a table with

Date Holiday Descriotion

And then the DAX code of the Date table will have to be slightly adjusted, you can send me a message on that and I shall support you

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Facing challenge to calculate previous year QTD

v-priyankata — Tue, 05 May 2026 08:16:29 GMT

Hi Soumeli

Thank you for reaching out to the Microsoft Fabric Forum Community.

Natarajan_M RDewi pcoley FBergamaschi Thanks for the inputs.

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.

Re: Facing challenge to calculate previous year QTD

v-priyankata — Fri, 08 May 2026 09:45:15 GMT

Hi Soumeli

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.