Forum Discussion
Dynamically display Revenue for CURRENT QTD in a table without dates - Measure
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
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
1 Reply
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