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

KRISH80 — Thu, 09 Jan 2020 03:27:57 GMT

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"))

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

kentyler — Sun, 12 Jan 2020 14:18:33 GMT

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

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
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
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.
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.

topic Re: DATESYTD issue.. how to solve if i have records with dates greater than fiscal end date? in DAX Commands and Tips

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

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