cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

DatesAdd Function with Custom Date - Help Needed

Hello, 

I am using this custom date table and I need to perform some MTD, QTD and YTD calculations. I am using DatesADD function and it just does not seem to work. The result always gives the sum of the whole previous year

https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query 

 

Does anyone have any idea how I can perform these activities with a custom date calender? 

 

Best Regards,

Simon 

10 REPLIES 10
Super User IV
Super User IV

@SimonSeez , Are you using Date table.

Please refer this video where I  dicussed Why Time intelligence can fail - https://www.youtube.com/watch?v=OBf0rjpp5Hw

 

You need make sure 5 things. Please check.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hello @amitchandak  

 Yes, I am using a date table. Off to watch your video now 

@SimonSeez , in case that does not help. Also, the useful information provided by other super users/users does not help. then

Please share a sample pbix after removing sensitive data.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hello @amitchandak 

 

Educative video as expected. I subscribed to the channel as well. I have applied everything you said and I noticed you used a DatesYTD before you used the DatesAdd. I hadn't seen this used before so I tried it 

 

This was my previous measure - 

Calculate([Total Revenue], DateAdd('Date Table' [Date], -1, Quarter)) 

 

I modified it to seem like yours 

Calculate([Total Revenue], DatesQTD(DateAdd('Date Table' [Date], -1, Quarter))). Strangely this gave me a blank result 

 

I then remodified to this 

Calculate([Total Revenue], DatesQTD(DateAdd('Date Table' [Date], -1, Year))) and strangely it gave me the correct result. I couldn't make sense of my the measure worked so I have some doubts. 

 

Any ideas why? 

 

@SimonSeez , Thanks for subscribing.

 

Calculate([Total Revenue], DatesQTD(DateAdd('Date Table' [Date], -1, Quarter)))  means last qtr. That is blank means no data in last qtr but data in last year same qtr.

 

That sound like strange.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

One quick question before I share data. 

 

I modified the measure to use the date that is in the same table as the revenue but instead of getting the total of last quarter I got the value of October 2020 

 

Calculate([Total Revenue], DatesQTD(DateAdd('DWH Revenue'[Date], -1, Quarter))).

 

 

@SimonSeez , dateadd need continuous dates . And it return blank when it does not get it.

refer this video, where I have shown that for a column - https://www.youtube.com/watch?v=9qiRivlBv8w

 

The best way test is

Calculate(Min('DWH Revenue'[Date]), DatesQTD(DateAdd('DWH Revenue'[Date], -1, Quarter)))


Calculate(Max('DWH Revenue'[Date]), DatesQTD(DateAdd('DWH Revenue'[Date], -1, Quarter)))

 

Now you know what you time intelligence function returns: min and max. It returns a set of dates.

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User IV
Super User IV

Are you using an approach like these?

Calculating MTD, QTD, YTD, Running and Cumulative Total in Power BI (mssqltips.com)

Basics of Time Intelligence in DAX for Power BI; Year to Date, Quarter to Date, Month to Date - RADA...

 

Please share your DAX for a specific suggestion.

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hello Pat, 

 

I am using this 

 

Calculate([Total Revenue], DateAdd('Date Table' [Date], -1, Quarter))

 

Rather than give me total for the last quarter, it gives a total for the whole of the previous year

Super User II
Super User II

@SimonSeez 

 

Using DAX you can use the following formulas for YTD and YTD LY

 

YTD = CALCULATE(SUM(Table6[Amount]),DATESYTD('Calendar'[Date]))

YTD LY = CALCULATE(Table6[YTD],SAMEPERIODLASTYEAR('Calendar'[Date]))

 

Useful links that you could look at are the following:

https://simplesqlbi.home.blog/2019/08/18/dax-formulas-to-calculate-mtd-qtd-ytd-values-and-comparing-...

 

https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors