Forum Discussion

New_hello188's avatar
New_hello188
Helper I
2 years ago
Solved

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.

 

 

 

Any help would be appreciated.

Thank you!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      New_hello188
      Helper I
      2 years ago

      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 !