Forum Discussion

Burubear's avatar
Burubear
Icon for Helper I rankHelper I
6 years ago
Solved

Calculate the previous date for each item

Hi All,

 

I want to create a Card graph wherein the Score for this specific team for a specific Quarter Shows

Splicers would be Year and Quarter. Then Card shows Score of each team (team A and B)

 

For the query question, the solution I can think of is that i want to create a column (not a measure since there would be no total calculation of Scores) for Previous quarter Dates. I have a "Logging Date" wherein users log their Score but it's for a previous quarter.

 

Data sample below is the calculated quarter based on the date:

DateTeamScore (1-10)YearPrefix
5/2/2019Team A62019Q2
5/10/2019Team B72019Q2
8/4/2019Team A62019Q3
8/15/2019Team B82019Q3
10/2/2019Team A92019Q4
10/2/2019Team B92019Q4
1/3/2020Team A82020Q1
1/10/2020Team B102020Q1

 

I would like to create a column showing that this Score is actually for a previous quarter (see 2 columns below) 

DateTeamScore (1-10)YearPrefixPrev QtrPrevious Qtr Yr
5/2/2019Team A62019Q2Q12019
5/10/2019Team B72019Q2Q12019
8/4/2019Team A62019Q3Q22019
8/15/2019Team B82019Q3Q22019
10/2/2019Team A92019Q4Q32019
10/2/2019Team B92019Q4Q32019
1/3/2020Team A82020Q1Q42019
1/10/2020Team B102020Q1Q42019

 

I have tried doing the basic conditional formatting wherein if Quarter shows Q4 then change to Q3, Q3 to Q2, etc. but When doing a splicer for Quarter and Year, the date for 1/3/2020 and 1/10/2020 should be for Q4 2019 not Q4 2020.

 

I'm not sure if there's any other way around with the final outcome that I want. I'm okay with a different approach.

Need Help

1 Reply

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Burubear ,

     

    Please create calculate columns as below.

    YEAR pre = YEAR(EDATE('Table'[Date],-3))
    Q pre = "Q" & QUARTER(EDATE('Table'[Date],-3))

    Pbix as attached.