Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
New_hello188
Helper I
Helper I

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

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.

New_hello188_0-1710905353387.png

 

New_hello188_1-1710905722047.png

 

 

Any help would be appreciated.

Thank you!

1 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

vrongtiepmsft_0-1711013691943.png

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.

View solution in original post

10 REPLIES 10
v-rongtiep-msft
Community Support
Community Support

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

 

vrongtiepmsft_0-1710990782976.png

 

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.

 

 

 

 

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.

New_hello188_0-1711002825935.png

 

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 ! 

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.

vrongtiepmsft_0-1711004088652.png

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

vrongtiepmsft_1-1711004151539.png

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.

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

vrongtiepmsft_0-1711005440581.pngvrongtiepmsft_1-1711005452657.png

 

 

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.

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

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.

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.

 

New_hello188_0-1711010074069.png

 

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

vrongtiepmsft_0-1711013691943.png

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.

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors