Dynamically display Revenue for CURRENT QTD in a table without dates - Measure

[Vander1981]

Hello,

Does someone know how to achieve the following  ?

Output table : Products in row vs Revenue in column.

I would like to create a measure that displays current QTD figures in a table that does not contain dates in the output table AND with possibility to filter by previous quarters as well.

While for YTD and Month it is fairly easy. I cannot make it for QTD.

Thanks a lot !

Ar

[v-yuezhe-msft · Accepted solution]

@Vander1981,

Based on my test, we can take the following steps to get the result that you want.

1. Enter the data and create a time table and create relationship between the two table based on dates.

2. To get the current and previous QTD, we can use the formulas to create a measure:

Current QTD := CALCULATE(SUM(Table1[Re]), FILTER(Table1, FORMAT(Table1[Date], "q")=FORMAT(NOW(),"q")))

Previous QTD := CALCULATE(SUM(Table1[Re]), FILTER(Table1, FORMAT(DATEADD(dimtime[Date],1,QUARTER), "q")=FORMAT(NOW(),"q")))

3. Also we can create some columns for reference if necessary.

previous quarter = YEAR(DATEADD(dimtime[Date], -1,QUARTER))& "Q" & FORMAT(DATEADD(dimtime[Date], -1,QUARTER),"q")

Q = YEAR(Table1[Date]) &"Q" & FORMAT(Table1[Date], "q")

After that we can get a table like this:

For more information, please refer to the pbix as attached.

Regards,
Lydia