Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
KRISH80
Helper II
Helper II

DATESYTD issue.. how to solve if i have records with dates greater than fiscal end date?

Dear All,

My issue is with DATESYTD. My report perfectly works if i have records with dates within the fiscal year. The moment i add records with dates greater than fiscal year end date, all my charts go off . DATESYTD stops functioning. Our FY starts on 01-Apr-2019 and ends on 31-Mar-2020. I have records with dates in April 2020. If i remove the records of April 2020 my report works perfectly fine, the momet i add everything goes off. Please help with solution . Or any alternate functions that i shuld use. Screenshots of the table below. The first image where it works fine as i have records with date less than or equal to 31-Mar-2020.  The second image is where it goes off when i adda records with dates greater than 31-Mar-2020. I can't eliminate those records as we always provide next 90days Forecast as well.

 

Formula is use is as below : 

RGN ACV Budget YTD = CALCULATE([RGN ACV Budget], DATESYTD('Calendar'[Date],"03/31"))
Sales Fiscal YTD = CALCULATE([TOTAL-WINS], DATESYTD('Calendar'[Date],"03/31"))

Untitled.png

Error.png

1 REPLY 1
kentyler
Solution Sage
Solution Sage

It may be that you need to use DATESBETWEEN(), where you calculate the start and end dates you want. Then the functions will work in the same way no matter what additional dates are present in your data. This might mean adding a parameter table to hold the start and end dates for your fiscal year.

 

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

  1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
  2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
  3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
  4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.




Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors