Forum Discussion

codyraptor's avatar
codyraptor
Resolver I
8 years ago

Calendar since Sept Update

Hey all.  I have a very simple Calendar Table connected to a date in my facts table.  Calendar table is built by using the built in function in Power BI.  I have had zero issues out of this in the past, but now my dates are not lining up for some reason.  My calendar table will show April, but the date from my facts table will show January?

 

This is the DAX formular I'm using...I'm getting a YTD amount as of 3 months ago.  The amount looks right..but it's removing the 1st 3 months out of my calendar instead of taking the last 3 months out.  Thoughts??  Never had this issue prior to the update.

 

Fee Collected 3 months ago = TOTALYTD(SUM(Dispatch[Fee Collected]),'Calendar'[Date],DATEADD('Calendar'[Date],-3,MONTH))

Need Help

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi codyraptor,

     

    The updates of Power BI Desktop are good. It's something wrong with your formula. The third parameter of TOTALYTD is a filter that would change the current context. 

    Fee Collected 3 months ago =
TOTALYTD (
    SUM ( Dispatch[Fee Collected] ),
    'Calendar'[Date],
    DATEADD ( 'Calendar'[Date], -3, MONTH )
)

    And it means moving back three months. Therefore, if these altered dates aren't in your Calendar, they will return blanks and hide in default.

     

    1. In my test, the first date is 2007-01-01. The three months are missed. If we see the year 2008, all the months are there.

    2. If we turn on "Show items with no data", we can see the missed months.

    3. I tested it in Desktop June, it's the same.

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

    • codyraptor's avatar
      codyraptor
      Resolver I
      8 years ago

      I'm not sure I understand how the issue is the formula...or I'm not understanding how the formula is working.  What should happen is I should receive a year to date as of 3 months ago....so Jan - Jul.  The values I'm receiving are correct and the calculation is working...but the months in my calendar are removing the 1st 3 months rather than Aug - Oct.  My calendar does not have any missing dates...so I'm not quite sure I follow.