cancel
Showing results for
Did you mean:
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.

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.

Tutorial Series Dax Vs SQL Direct Query PBI Tips

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!

Announcements

Welcome to the User Group Public Preview

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