cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cmow333
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
amitchandak
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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.