Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Time Intelligence Calculation Issue

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.

Need Help

4 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        v-kkf-msft
        Community Support
        5 years ago

        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