Time Intelligence Calculation Issue

[Anonymous]

Greetings One & All, With reference to the table above, I am trying to calculate YTD,MTD and so on but the result I obtain does not seem to be accurate because it includes the default 1/0/1900 when I use the "Referral Date" column in the YTD calculation. Is there a way to calculate the YTD &MTD etc without including the default 1/0/1900 dates?? I have a date table included in the data model but it doesn't work effectively. Any ideas from the forum will be greatly appreciated.

[v-kkf-msft]

Hi @Anonymous ,

Can you provide the DAX formula you are using? Can you provide more sample data and the output results you want?

Best Regards,
Winniz

[Anonymous]

@v-kkf-msft Many thanks for your willingness to help. The DAX formula I used is as follows: MTD =TOTALMTD(COMBO1[APS],(COMBO1[REFRERRAL DATE])
YTD=TOTALYTD(COMBO1[APS],(COMBO1[REFERRAL DATE])

This is what I am trying to achieve in terms of output results:

I hope this clarifies what I am asking help with. Thanks

[v-kkf-msft]

Hi @Anonymous ,

I want to know how you successfully changed the type of column REFRERRAL DATE? Because the function TOTALMTD/TOTALYTD need to refer to the date column, when I convert column REFRERRAL DATE into date type, the cell with the value "1900-1-0" will be wrong.

So the table I get after conversion is as shown in the figure below.

Can you share your PBIX file that does not contain sensitive data?

Best Regards,
Winniz

[Anonymous]

Hello Winniz,

I did not have any issues with the date because I formated the excel file with the date column as date. Again you can find the tranform tab in powerbi desktop and change the column to date format.

Unfortunately, my file contains sensitive data so I cannot share it. 

Thanks for your help