Retrieve data from the previous quarters using a QQ-YYYY formatted column.

[New_hello188]

Hello Community Members,

I'm reaching out for your help and advice. I need to retrieve data from the previous quarter and the two quarters before that, based on a QQ-YYYY formatted column.

Here's how it works:

• The 'Quarter Revenue' column determines the quarter, depending on the 'Scope' column. If 'Scope' is 'Yes', it returns the current quarter in QQ-YYYY format. If 'Scope' is 'No', it's based on the promise date column in QQ-YYYY format.

Quarter_Revenue =
var current_dat =  "Q" & FORMAT(TODAY(),"Q") & "-" & FORMAT(TODAY(),"YYYY")
var promise_dat = "Q" & FORMAT(Table1[Promise date ],"Q") & "-" & FORMAT(Table1[Promise date ],"YYYY")
return

if(Table1[Scope ] = "Yes",current_dat,promise_dat)

I've linked the 'Quarter Revenue' column with a Qtr-Year format column in the Date table to sort it correctly. However, I'm unsure how to get the amounts from the previous and the last two quarters using this setup.

Any help would be appreciated.

Thank you!

[v-rongtiep-msft]

Hi @New_hello188 ,

I have create a simple sample, please refer to my pbix file to see if it helps you.

Create 2 columns.

last quarter =
VAR _year =
    YEAR ( TODAY () )
VAR _lasryear = _year - 1
VAR _quarter =
    QUARTER ( TODAY () )
RETURN
    IF (
        _quarter = 1,
        "Q" & 4 & "-" & _lasryear,
        IF ( _quarter > 1, "Q" & _quarter - 1 & "-" & _year )
    )

last2quarter =
VAR _year =
    YEAR ( TODAY () )
VAR _lasryear = _year - 1
VAR _quarter =
    QUARTER ( TODAY () )
RETURN
    IF (
        _quarter = 2,
        "Q" & 4 & "-" & _lasryear,
        IF (
            _quarter = 1,
            "Q" & 3 & "-" & _lasryear,
            IF ( _quarter >= 3, "Q" & _quarter - 2 & "-" & _year )
        )
    )

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[New_hello188]

Hi Rongtie,

Thank you very much for your reply; it is much appreciated.

I have reviewed the file provided by you and found some inconsistencies, one of which I have highlighted in red.

For example, on the date 12Jul23, the last quarter measure should return Q2-2023, while the last 2 quarters should return Q1-2023. The same concept should be applied to the rest.

For better understanding , please help to review my test pbix file. Any idea to get previous quarter and last 2 quarter based on the column Quarter-Revenue ? 

Here is my test file with sample data  : 

test pbix file  

Thank you ! 

[v-rongtiep-msft]

Hi @New_hello188 ,

I was unable to download the pbix file based on the link you gave. I have modified the formula as per your request.

The columns in the table get the previous two quarters based on today's date.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[New_hello188]

Hi Rongtie , 

Sorry my bad , here is the link 

https://www.dropbox.com/scl/fo/hsyfk0bdjk00mbw11epyh/h?rlkey=rzej9kb54x5b3mx3r1kc052ev&dl=0

[v-rongtiep-msft]

Hi @New_hello188 ,

Still can't.

You can also provide some sample data like the following:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

Best Regards
Community Support Team _ Rongtie

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

[New_hello188]

Hi RongTie ,

Thanks for fast response. 

Could you please help to access this again ? I moved the file to google drive 

https://drive.google.com/file/d/1bC_N6dqFvwsXu4I_W-dwaXYR2_KhNiQY/view?usp=sharing

[v-rongtiep-msft]

Hi @New_hello188 ,

Still can't.

Best Regards
Community Support Team _ Rongtie

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

[New_hello188]

Hi @v-rongtiep-msft ,

If you're able to view this page, could you please click the download button ( circled in red) ? This will initiate the download of the PBIX file containing the text.

[v-rongtiep-msft · Accepted solution]

Hi @New_hello188 ,

Please refer to my pbix file.

If you want to put last quarter's value into card, this measure you wrote won't work because it's getting value based on each [promise date] in the table and can't be put into card visual, you can create a measure.

Measure = 
VAR _cur_year_quarter = CALCULATE(MAX('Date Table'[YearQuarterSort]),'Date Table'[Date]=TODAY())
VAR _result = CALCULATE(SUM('Table1'[Amount]),'Date Table'[YearQuarterSort]=_cur_year_quarter-1)
RETURN
_result

Best Regards
Community Support Team _ Rongtie

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

[New_hello188]

Hi @v-rongtiep-msft , 

Thanks for the file and your support. I've used your measure in my work, and it's working perfectly.

One key takeaway from this is the importance of consistent date formats across tables to avoid incorrect results.

Thanks again!