Facing challenge to calculate previous year QTD

[Soumeli]

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,

                        0

                       ) * 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.

[v-priyankata]

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.

[v-priyankata]

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.

[RDewi]

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

    )

)

[pcoley]

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.

[Soumeli]

Hi @pcoley 

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

[Natarajan_M]

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!

[Soumeli]

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?

[FBergamaschi]

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(

        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

[Soumeli]

Hi Team,

While converting a report from 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,

                        0

                       ) * 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.