Quarterly and Year sum is giving previous quarter and year sum if the value is empty.

[Anonymous]

Hi,

Need your expert help.

I am trying to get the total Quarterly and Yearly which I achieved, however it is giving the data for previous quarter and previous year when the return value is empty for current quarter and year.

I used below formulas where Date is coming from calendar table which is connected to fact table with a datekey.

Quarterly CMRR = TOTALQTD(SUM('New & Existing'[Total Incremental MRR (converted)]),'Date'[Date])

Yearly CMRR = TOTALYTD(SUM('New & Existing'[Total Incremental MRR (converted)]),'Date'[Date])

[v-yulgu-msft]

Hi @Anonymous,

Maybe you could wrap the measures with an IF statement.

Without detailed sample data, I cannot understand your requirement well. From above expected table, how should we know which row is in current year and quarter?

Regards,

Yuliana Gu

[Anonymous]

Hi @v-yulgu-msft

Thanks for the reply, I am able to fix this issue now 🙂

[v-yulgu-msft]

Hi @Anonymous,

Glad to hear that. Would you please share the valid solution so that it can help more users?

Regards,

Yuliana Gu

[Anonymous · Accepted solution]

Hi @v-yulgu-msft,

This also fixed with the same solution as below:

I fixed it by creating a unique Country table and related to the fact table, then I used that country to segment the total.

Alternatively I used column which will identify current month and used it as filter for MTD totals.