TotalMTD is not showing blank for categories which does not have any value for current month

[Anonymous]

TotalMTD is not showing blank for categories which does not have any value for current month

I used below formula it is working fine if there is value for country, but if there is zero value in current month it is showing previous qaurter instead of showing blank or zero.

Total Incremental MRR (converted) MTD =
IF(
ISFILTERED('Date'[Date]),
TOTALMTD(
SUM('ITX New & Existing'[Total Incremental MRR (converted)]),
'Date'[Date]
)
)

[v-shex-msft]

HI @Anonymous,

AFAK, time intelligence function only works on records who existed in current calendar.

For nonexistent records, you need to write formula to manually calculate and filter on these date range

Reference link:
Time Intelligence in Power BI Desktop

Problem with Time Intelligence functions

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin,

Relationship model is set with datekey, however I am getting the results but for Monthly results it is giving me last available value.

Let me show you the screenshot. Appreciate your kind help please. Thank you.

Formulas used as below :

Monthly CMRR = TOTALMTD(SUM('ITX New & Existing'[Total Incremental MRR (converted)]), 'Date'[Date])

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

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

[Floriankx]

Hello,

your formula seems fine.

How is your [Total Incremental MRR (converted)] calculated?

Did you use DateValues from DateTable for Filter/slicer purpose?

You can try to vary the Cross Filter Direction. I'm not sure if both is correct with this application.

Best regards.

[Anonymous]

Hi,

[Total Incremental MRR (converted)]  this value is directly coming from fact table which is simple decimal numbers.

I tried date values from date table as well as from fact table still the results seems same.

Could you please help me with Cross Filter Direction, not sure how to achieve it if this solves my problem.

Thank you 

Best Regards

[Floriankx]

Hello,

it is in your 'Edit Relationship' tab you screenshotted.

Should be a Dropdown menu.

[Anonymous]

Hi,

Yes, I did tested with multiple options in filter direction with single and both, but it seems to be of not expected result.

I am not sure what I am doing wrong.

Thanks 

Best Regards,

[v-shex-msft]

HI @Anonymous,

I think your relationship from fact to calendar is based on non date value, right?
In addition, please share pbix file for test, it will be help for troubleshooting.

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Hi @v-shex-msft

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.