Creating a measure to show only previous quarter values

[aga_aga]

Hello, 

In my table about posted job ads, I have a date column of when the job ads were closed. 
Currently, I need to show in my report how many jobs were closed in Q4 2024. In my table I have data for the whole 2024 and also some data for Q1 2025. 

I need to create a measure that will show values only for the Q4 2024. I will be refreshing the report quaterly and I need the report torefres automatically and show me data only for the previous quarter, and not include the data from exsisting quarter. 
It's not a problem to create a measure to show me data for a required quarter, my problem is that I want a measure that will automatically adjust to show previous quarter each time the report is refreshed.  Next time I will be refereshing it in April and I want it to be ajusted automatically to show Q1 2025 data. 

Could anyone help me with this please? I've tried to create measure using different date functions but I haven't succeded... 

[DataNinja777 · Accepted solution]

Hi @aga_aga ,

To create a measure that dynamically adjusts to show job ads closed in the previous quarter, you need a DAX formula that determines the latest available date in your dataset and calculates the corresponding previous quarter. The key is to extract the latest date, determine its year and quarter, and then compute the previous quarter’s date range.

Closed Jobs Previous Quarter = 
VAR MaxDate = MAX('JobAds'[Closed Date])  
VAR CurrentYear = YEAR(MaxDate)
VAR CurrentQuarter = QUARTER(MaxDate)

VAR PreviousQuarter = IF(CurrentQuarter = 1, 4, CurrentQuarter - 1)
VAR PreviousYear = IF(CurrentQuarter = 1, CurrentYear - 1, CurrentYear)

RETURN 
CALCULATE(
    COUNT('JobAds'[Job ID]), 
    'JobAds'[Closed Date] >= DATE(PreviousYear, (PreviousQuarter - 1) * 3 + 1, 1) &&
    'JobAds'[Closed Date] < DATE(PreviousYear, PreviousQuarter * 3 + 1, 1)
)

This measure first determines the maximum date from the dataset and extracts the corresponding year and quarter. Then, it calculates the previous quarter, adjusting for cases where the current quarter is Q1 by rolling back to Q4 of the previous year. The CALCULATE function then filters the data for the computed previous quarter’s date range. When the report is refreshed, this measure will always adjust automatically based on the latest available data, ensuring that it displays only the job ads closed in the previous quarter without including data from the current one.

Best regards,

[aga_aga]

Thank you so much for your responde DataNinja777 and a very clear explanation. You made my day 🙂