Calculate the previous date for each item

[Burubear]

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:

Date	Team	Score (1-10)	Year	Prefix
5/2/2019	Team A	6	2019	Q2
5/10/2019	Team B	7	2019	Q2
8/4/2019	Team A	6	2019	Q3
8/15/2019	Team B	8	2019	Q3
10/2/2019	Team A	9	2019	Q4
10/2/2019	Team B	9	2019	Q4
1/3/2020	Team A	8	2020	Q1
1/10/2020	Team B	10	2020	Q1

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

Date	Team	Score (1-10)	Year	Prefix	Prev Qtr	Previous Qtr Yr
5/2/2019	Team A	6	2019	Q2	Q1	2019
5/10/2019	Team B	7	2019	Q2	Q1	2019
8/4/2019	Team A	6	2019	Q3	Q2	2019
8/15/2019	Team B	8	2019	Q3	Q2	2019
10/2/2019	Team A	9	2019	Q4	Q3	2019
10/2/2019	Team B	9	2019	Q4	Q3	2019
1/3/2020	Team A	8	2020	Q1	Q4	2019
1/10/2020	Team B	10	2020	Q1	Q4	2019

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.

[v-frfei-msft · Accepted solution]

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.