cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Newbie looking for help - guessing this will be simple for someone to solve

Hello - I am new to PowerBI/DAX and have a seemingly simple problem that I have been fighting for a while.   Have been doing a bunch of searching and just can't seem to solve my issue.

 

I have a simple data set with only 3 fields.   A date representing an entry date, a date representing an accounting date which is related to the entry date as well as the amount of sales for the entry date.   My end users can't really relate to the accounting date even though they are aware of how it works.   What I want to show them is the YTD sales for any entry date and that is defined as the YTD sales based on the accounting date related to the entry date.   I have that portion working correctly by using the following measure:

 

YTDSales = CALCULATE(SUM(Sheet1[DailySales]),DATESYTD(Sheet1[AccountingDate]), all(Sheet1[CalendarDate]))
 
I also want to show the prior years sales amount in comparison.   This also would be based on comparing the sales through an accounting date vs the sales through the same accounting date last year.   My measure formula for this is:
 
PriorYTDSales = CALCULATE(SUM(Sheet1[DailySales]),DATESYTD(DATEADD(Sheet1[AccountingDate],-1,YEAR)), all(Sheet1[CalendarDate]))
 
This formula seems to work correctly EXCEPT when there was not any sales last year on the same accounting date (sales only occur monday-friday, so there aren't sales everyday)  For those dates, I get a blank return value.   I don't understand why this would occur, as I would think based on the formula I would be summing up every date prior to that date, which should provide a value.
 
I have also tried using a date table and built in time intelligence functions, and have had issues with that strategy as well.   
 
 
Any help/guidance anyone could give would be appreciated.   For my education, if you could possibly explain why this is not working as well as put me on the right path to solve, I would be grateful.
 
 
1 REPLY 1
Super User IV
Super User IV

@cmow333 , In datesytd you should you date from calendar

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.



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!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors