DAX measure

[DanduMani104]

I Have 2024 and 2025 data in my Power bi desktop. I want to show the Distinct Count of tour requests Count By Rolling Quarter View. It means We Have to take current week from Current year table ,It falls under Q1 Quarter and After completing Q1 it should come Next year Q1 count of Next year like that . I have used this DAX measure for 2024 tour count.

 CALCULATE( DISTINCTCOUNT(Append_QMR[TOUR_REQUEST_NO]),

    FILTER(

        'Append_QMR',

        'Append_QMR'[WEEK_NO] = WEEKNUM(TODAY())

            && 'Append_QMR'[DEPARTURE_YEAR] = YEAR(TODAY())

            && 'Append_QMR'[Year-] = YEAR(TODAY())

            && 'Append_QMR'[QT_EXCLUDE_FLG] = 0

            && 'Append_QMR'[SALES_OFFICE_KEY] IN VALUES(M_OFFICE_D[OFFICE_KEY])

            && 'Append_QMR'[TOUR_TYPE_KEY] IN VALUES('M_TOUR_TYPE_D'[TOUR_TYPE_KEY])

            && 'Append_QMR'[TERMINATION_DATE_KEY] IN VALUES('M_DATE_D'[DATE_KEY])

            &&  'Append_QMR'[DEPARTURE_MONTH_KEY] <= 12

            ))+0

The above imahe is for 2024.

But Quarter should be change Dynamically ..
For Example, after completing Q1 of 2024 it should appear Q1 of 2025 like rolling Quarter.

[v-jialongy-msft]

Hi @DanduMani104 

To create a rolling quarter view in Power BI that dynamically changes each quarter, you need a DAX measure that can understand both the current date and how it relates to the quarters of each year in your data. The measure you've provided is only focused on the current week and year.

Here's a revised version of your DAX measure that might work for your rolling quarter view:

RollingQuarterCount =
VAR CurrentYear = YEAR(TODAY())
VAR CurrentQuarter = QUARTER(TODAY())
VAR PreviousYear = CurrentYear - 1
VAR QuarterToCompare = IF(CurrentQuarter = 1, 4, CurrentQuarter - 1)
VAR YearToCompare = IF(CurrentQuarter = 1, PreviousYear, CurrentYear)

RETURN
CALCULATE(
DISTINCTCOUNT(Append_QMR[TOUR_REQUEST_NO]),
FILTER(
'Append_QMR',
(YEAR(Append_QMR[DEPARTURE_DATE]) = YearToCompare && QUARTER(Append_QMR[DEPARTURE_DATE]) = QuarterToCompare)
|| (YEAR(Append_QMR[DEPARTURE_DATE]) = CurrentYear && QUARTER(Append_QMR[DEPARTURE_DATE]) = CurrentQuarter)
&& 'Append_QMR'[QT_EXCLUDE_FLG] = 0
&& 'Append_QMR'[SALES_OFFICE_KEY] IN VALUES(M_OFFICE_D[OFFICE_KEY])
&& 'Append_QMR'[TOUR_TYPE_KEY] IN VALUES('M_TOUR_TYPE_D'[TOUR_TYPE_KEY])
&& 'Append_QMR'[TERMINATION_DATE_KEY] IN VALUES('M_DATE_D'[DATE_KEY])
&& 'Append_QMR'[DEPARTURE_MONTH_KEY] <= 12
)
) + 0

^{Best Regards,}

^Jayleny

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

[DanduMani104]

want to compare for next year not previous year

[v-jialongy-msft]

Hi @DanduMani104 

Change the CurrentYear-1 in the code to CurrentYear+1 is the data for next year.

^{Best Regards,}

^Jayleny

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

[DanduMani104]

I modified it like this

RollingQuarterCount =

VAR CurrentYear = YEAR(TODAY())

VAR CurrentQuarter = QUARTER(TODAY())

VAR NexYear = CurrentYear +1

VAR QuarterToCompare = IF(CurrentQuarter = 1, 4, CurrentQuarter + 1)

VAR YearToCompare = IF(CurrentQuarter = 1, NexYear, CurrentYear)

RETURN

CALCULATE(

DISTINCTCOUNT(Append_QMR[TOUR_REQUEST_NO]),

FILTER(

'Append_QMR',

(YEAR(Append_QMR[DEPARTURE_YEAR]) = YearToCompare && QUARTER(Append_QMR[DEPARTURE_YEAR]) = QuarterToCompare)

|| (YEAR(Append_QMR[DEPARTURE_YEAR]) = CurrentYear && QUARTER(Append_QMR[DEPARTURE_YEAR]) = CurrentQuarter)

&& 'Append_QMR'[QT_EXCLUDE_FLG] = 0

&& 'Append_QMR'[SALES_OFFICE_KEY] IN VALUES(M_OFFICE_D[OFFICE_KEY])

&& 'Append_QMR'[TOUR_TYPE_KEY] IN VALUES('M_TOUR_TYPE_D'[TOUR_TYPE_KEY])

&& 'Append_QMR'[TERMINATION_DATE_KEY] IN VALUES('M_DATE_D'[DATE_KEY])

&& 'Append_QMR'[DEPARTURE_MONTH_KEY] <= 12

)

) + 0
But it comes like this

[DanduMani104]

But I dont have DEparture Data Column, I have Departure Year column

[v-jialongy-msft]

 Hi @DanduMani104 

Can you provide detailed sample data and the results you expect? So that I can help you better. Show it as a screenshot or excel. Please remove any sensitive data in advance.

^{Best Regards,}

^Jayleny

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

[DanduMani104]

Like this , The above image is 46th week of 2023 and it is fall in Q4 ,after that Q1,Q2,Q3 for 2024 are there like this..I want to represent in power bi